beckham beckham - 3 months ago 8
SQL Question

how to join 2 tables based upon Dates

I have two tables.
1st Table looks like below

session_start_dt | package_key
-------------------------------------------
01-01-2015 | 202
01-02-2015 | 203
01-14-2016 | 204
12-01-2015 | 206
03-02-2015 | 204
04-14-2016 | 202


2nd table looks like below

package_key | term_start_date | term_end_date | Active | event
202 | 01-01-2014 | 01-01-2015 | Y | NS
202 | 01-01-2014 | 01-01-2015 | Y | CAN
203 | 01-02-2015 | 01-02-2016 | N | NS
204 | 01-02-2015 | 01-02-2016 | N | NS
206 | 01-02-2015 | 01-02-2016 | N | NS
206 | 01-01-2014 | 01-01-2015 | Y | NS


I need to get all the records from the 1st table and get if he is active or not from the 2nd table when the session_start_dt falls between the term_start_date and term_end_date of the 2nd table.

session_start_dt | package_key | Active
-------------------------------------------
01-01-2015 | 202 | Y
01-02-2015 | 203 | N
01-14-2016 | 204 | null
12-01-2015 | 206 | N
03-02-2015 | 204 | N
04-14-2016 | 202 | null


I am writing the query like

select package_key, session_start_dt from table a, table b
where SESSION_START_Dt BETWEEN TERM_START_DATE AND TERM_END_DATE AND
PACKAGE_KEY = PACKAGE_KEY and event not in ('CAN')


and it's not giving the desired results.

Answer

You can use between for join conditions. I think this is the logic you want:

select t1.*, t2.active
from table1 t1 left join
     table2 t2
     on t1.package_key = t2.package_key and
        t1.session_start_dt between t2.term_start_date and t2.term_end_date;