CODEWITHSUNDEEP
sqlsql-serverdatejoinmerge
EmShing
EmShing

Reputation: 29

Merge when date is within a date range in another dataset in SQL

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

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You need record from A and latest but minimum than A's date from Table B.

You can use the analytical function as fllows:

select * from
(select a.*, B.*,
       row_number() over (partition by a.date order by b.start_date desc) as rn
  from tablea a join tableb b on a.date > b.start_date) t
where rn = 1

-- Update

I think you want the record and next record from B and then compare it with A then you can use the LEAD as follows:

select a.*, b.start_date, b.income
  from tablea a 
  join (select b.*, lead(start_date) over (order by start_date) as end_Date 
          from tableb b) b 
    on a.date between b.start_Date and b.end_date 
    or (a.date > b.start_date and b.end_Date is null)

Upvotes: 1

Related Questions