I have two table that I am trying to join(see below). Each have the same four columns on which I need to join. The second is the complete list, while the first may be missing some records.
Where the records are missing I want to use the lowest ROWNUM
that matches the other three. I am failing to see a way through the woods on this one.
DECLARE @TEMP AS TABLE ( OCC_CODE VARCHAR(2), ACCOUNTNO VARCHAR(36), IMPNO NUMERIC(14,0), TAX_YEAR NUMERIC(4,0), ROWNUM NUMERIC(1,0) ) INSERT INTO @TEMP VALUES ('T1','A1',1,2018,1), ('T2','A1',1,2019,1), ('T3','A1',1,2020,1), ('T4','A1',1,2020,2), ('T5','A1',1,2021,1) DECLARE @TEMP2 AS TABLE ( SEG_ID NUMERIC(11,0), ACCOUNTNO VARCHAR(36), IMPNO NUMERIC(14,0), TAX_YEAR NUMERIC(4,0), ROWNUM NUMERIC(1,0) ) INSERT INTO @TEMP2 VALUES (1,'A1',1,2018,1), (2,'A1',1,2019,1), (3,'A1',1,2020,1), (4,'A1',1,2021,1), (5,'A1',1,2018,2), (6,'A1',1,2019,2), (7,'A1',1,2020,2), (8,'A1',1,2021,2) select TT.SEG_ID,TT.ACCOUNTNO,TT.IMPNO,TT.TAX_YEAR,oc.OCC_CODE FROM @TEMP2 TT left JOIN @TEMP OC ON TT.ACCOUNTNO = OC.ACCOUNTNO AND TT.IMPNO = OC.IMPNO AND TT.TAX_YEAR = OC.TAX_YEAR AND TT.ROWNUM = OC.ROWNUM ORDER BY TAX_YEAR,SEG_ID
This returns:
I am trying to get T1
in Line 2, T2
in line 4 and T5
in line 8.
As you can see with TAX_YEAR
2020 sometimes there is info that needs to match on the ROWNUM
that is different, so I cannot remove the AND TT.ROWNUM = OC.ROWNUM
Advertisement
Answer
If I understand you correctly, a lateral join (or a subquery) with an inequality condition does what you want:
select tt.seg_id, tt.accountno, tt.impno, tt.tax_year, oc.occ_code from @temp2 tt cross apply ( select top (1) oc.occ_code from @temp oc where tt.accountno = oc.accountno and tt.impno = oc.impno and tt.tax_year = oc.tax_year and tt.rownum >= oc.rownum order by oc.rownum desc ) oc order by tax_year,seg_id
Basically the lateral join searches for an exact match, and falls back on the first match in smaller rownum
s.
seg_id | accountno | impno | tax_year | occ_code -----: | :-------- | ----: | -------: | :------- 1 | A1 | 1 | 2018 | T1 5 | A1 | 1 | 2018 | T1 2 | A1 | 1 | 2019 | T2 6 | A1 | 1 | 2019 | T2 3 | A1 | 1 | 2020 | T3 7 | A1 | 1 | 2020 | T4 4 | A1 | 1 | 2021 | T5 8 | A1 | 1 | 2021 | T5