CODEWITHSUNDEEP
sqljoin
BobBauer
BobBauer

Reputation: 1

Join table to value linked to closest date

I am struggling with this problem in SQL server. So I have two tables:

TableA:

ID_A ID_B Donation_date Donation_amount
1 2 15/03/2022 3000
1 2 25/04/2021 5000

and TableB

ID_A ID_B Expense_date Expense_amount
1 2 17/03/2022 50000
1 2 20/04/2021 70000
1 2 01/03/2015 180000

I want to add a column to TableA that contains a reference expense for each ID, based on the closest date to the dates in TableB.

The output would look like this:

ID_A ID_B Donation_date Donation_amount Ref_Expense_date Ref_expense_amount
1 2 15/03/2022 3000 17/03/2022 50000
1 2 25/04/2021 5000 20/04/2021 70000

I am almost certain that I have to use join, but not sure how to do it based on the "closest date". Any help would be much appreciated!

Upvotes: -1

Views: 785

Answers (1)

DannySlor
DannySlor

Reputation: 4620

I joined the tables by ID_A and ID_B and then organized the date by the smallest difference in dates using abs, datediff, and row_number.

select  t.ID_A
       ,t.ID_B
       ,Donation_date
       ,Donation_amount
       ,Ref_Expense_date
       ,Ref_Expense_amount

from   (
        select TableA.ID_A
              ,TableA.ID_B
              ,Donation_date
              ,Donation_amount
              ,Expense_date   as Ref_Expense_date
              ,Expense_amount as Ref_Expense_amount
              ,row_number() over(partition by TableA.ID_A,TableA.ID_B, Donation_date order by abs(datediff(day, Donation_date,Expense_date))) as rn
        from TableA full join TableB on TableB.ID_A = TableA.ID_A and
                                        TableB.ID_B = TableA.ID_B

       ) t
where rn = 1
ID_A ID_B Donation_date Donation_amount Ref_Expense_date Ref_Expense_amount
1 2 2022-03-15 3000 2022-03-17 50000
1 2 2021-04-25 5000 2021-04-20 70000

Fiddle

Upvotes: 2

Related Questions