hi everyone. i have an scenario where i am storing range of date in a table. i want to read the range of date in that table to query another table
consider this data
with dt as (
select to_Date('01/01/2018','mm/dd/yyyy') start_dt, to_Date('07/01/2018','mm/dd/yyyy') end_dt from dual union all
select to_Date('09/01/2018','mm/dd/yyyy') start_dt, to_Date('12/01/2018','mm/dd/yyyy') end_dt from dual union all
select to_Date('01/01/2019','mm/dd/yyyy') start_dt, to_Date('03/01/2019','mm/dd/yyyy') end_dt from dual
)
, data as
(
select 'MIKE' name , to_Date('01/01/2018','mm/dd/yyyy') start_dt from dual union all
select 'SMITH' name , to_Date('03/01/2018','mm/dd/yyyy') start_dt from dual union all
select 'ROB' name , to_Date('06/01/2018','mm/dd/yyyy') start_dt from dual union all
select 'BOB' name , to_Date('10/21/2018','mm/dd/yyyy') start_dt from dual union all
select 'MEEK' name , to_Date('02/21/2019','mm/dd/yyyy') start_dt from dual union all
select 'KEVIN' name , to_Date('04/01/2019','mm/dd/yyyy') start_dt from dual union all
select 'ERV' name , to_Date('08/01/2018','mm/dd/yyyy') start_dt from dual
)
i want to display the data from data table for the range of date in dt table. for example, dt table has 1/1/201-7/1/2018, therefore MIKE, SMITH should be display.
then for 9/1/2018-12/1/2018 BOB should be display and so on. my final output should look like this
NAME START_DT
==========================
MIKE 1/01/2018
SMITH 3/01/2018
BOB 10/21/2018
MEEK 02/21/2019
the other names should be filter out since the start date does not fall under the range specified in dt table.
i started writing a query but only works for one range date. for example
select * from data where start_dt between (select start_dt from dt) and (select end_dt from dt)
this query will only work if there is one row in dt table. but since there are 3 rows i dont know how to handle that.
i am using oracle 11g. can someone help me modify the query so that i can select rows in data table based on the date ranges in dt table?
thanks in advance