Search code examples
sqlsql-servert-sqljoinsubquery

If Row from left joined table is null use another row

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:

enter image description here

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

Solution

  • 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 rownums.

    Demo on DB Fiddle:

    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