Bernardo Bernardo - 3 months ago 8
SQL Question

SQL join based on Date

I have two tables:
Table A

+-------+----------+
| prop | str_date |
+-------+----------+
| AL408 | 3/1/2009 |
| AL408 | 4/1/2009 |
| AL408 | 5/1/2009 |
| AL408 | 6/1/2009 |
+-------+----------+


Table B

+---------+-----------+----------+
| prop_id | agrx_date | brand_id |
+---------+-----------+----------+
| AL408 | 5/5/1986 | CI |
| AL408 | 6/30/1994 | CI |
| AL408 | 5/3/1999 | CI |
| AL408 | 4/21/2006 | CI |
| AL408 | 3/20/2009 | QI |
+---------+-----------+----------+


I'd like pull in brand_id into my result query but the brand_id changes accordingly by comparing str_date to agrx_date. For the month after a brand_id has changed via the agrx_date, the result would reflect that new brand_id. All str_dates are monthly values.

The end result would look like this:

+-------+----------+--------+
| prop | str_date | Result |
+-------+----------+--------+
| AL408 | 3/1/2009 | CI |
| AL408 | 4/1/2009 | QI |
| AL408 | 5/1/2009 | QI |
| AL408 | 6/1/2009 | QI |
+-------+----------+--------+


Here's what I have so far (which is not correct) and I'm not sure how to get my end result.

select
a.prop
,a.str_date
,b.agrx_date
,b.brand_id

from tableA a
left join tableB b
on a.prop = b.prop_id
and a.str_date < b.agrx_date

where a.prop = 'AL408'


I'm passing this through Tableau so I cannot use CTE or other temp tables.

Answer

You could create a date range using a lead() analytical function. The date range could then be used as part of a theta join to pull in the correct brand. This is a pretty simple way to pull the date value from the next record, see the definition of next_agrx_date below.

The range would be inclusive for the start (>=), but noninclusive on the end (<). You also need to handle the null case for open-ended ranges. You can find this logic in the join below.

select
   a.prop
  ,a.str_date
  ,b.agrx_date
  ,b.brand_id
from tableA a 
left join
( select 
     prop
    ,agrx_date
    ,brand_id
    ,lead(agrx_date) over (partition by prop order by agrx_date) next_agrx_date 
  from tableB ) b 
on (b.prop = a.prop and a.str_date >= b.agrx_date and (a.str_date < b.next_agrx_date or b.next_agrx_date is null))
order by prop, str_date
Comments