sql server - Merge when date is within a date range in another dataset in SQL

Question

I have 2 datasets: A and B

dataset A:

Date        | Age 
_____________________
31/3/2015   | 21
31/8/2015   | 30

dataset B:

StartDate   | Income
_____________________
30/3/2015   | 1k
31/7/2015   | 2k
1/10/2015   | 3k

How do I merge dataset A and B (left join B) so that for example, date 31/8/2015 from dataset A is within the date range of 31/7/2016 and 1/10/2015 in dataset B. So on the 31/8/2015, income would be 2k.

End result should look like this:

Date        | Age  | Income
__________________________________________
31/3/2015   | 21   | 1k
31/8/2015   | 30   | 2k

question from:https://stackoverflow.com/questions/65682281/merge-when-date-is-within-a-date-range-in-another-dataset-in-sql

Answer 1

It looks like a correlated subquery is one way to do what you want. In standard SQL, this look like:

select a.*,
       (select b.income
        from b
        where b.date <= a.date
        order by b.date desc
        fetch first 1 row only
       ) as income
from a;

Note: Not all databases support the fetch first clause. All support similar functionality, using limit, select top or something similar.

You can take a different approach if you like as well. If the second table had ranges where the income were know rather than a single date, then you could use a join:

select a.*, b.income
from a left join
     (select b.*, lead(date) over (order by date) as next_date
      from b
     ) b
     on b.date <= a.date and
        (b.next_date is null or b.next_date > a.date);

This approach has the advantage that it is easier to fetch multiple columns from b.