CODEWITHSUNDEEP
sqloracle-database
beckham
beckham

Reputation: 125

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.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 1

Related Questions