Find matching interval in SQL - Oracle

Question

I have a table:

table:

start         end
1 Jan 09    31 Jan 2009
1 Feb 09    28 Feb 2009
1 Mar 09    31 Mar 2009
1 Apr 09    01 May 2009
1 May 09    31 May 2009
1 Jun 09    01 Jul 2009
1 Jul 09    31 Jul 2009
1 Aug 09    31 Aug 2009
1 Sep 09    01 Oct 2009
1 Oct 09    31 Oct 2009
1 Nov 09    01 Dec 2009
1 Dec 09    31 Dec 2009
1 Jan 10    31 Jan 2010
1 Feb 10    03 Mar 2010
1 Mar 10    31 Mar 2010
1 Apr 10    01 May 2010
1 May 10    31 May 2010
1 Jun 10    01 Jul 2010
1 Jul 10    31 Jul 2010
1 Aug 10    31 Aug 2010
1 Sep 10    01 Oct 2010
1 Oct 10    31 Oct 2010
1 Nov 10    01 Dec 2010
1 Dec 10    31 Dec 2010
1 Jan 09    31 Mar 2009
1 Apr 09    30 Jun 2009
1 Jul 09    01 Oct 2009
1 Oct 09    31 Dec 2009
1 Jan 10    31 Mar 2010
1 Apr 10    30 Jun 2010
1 Jul 10    01 Oct 2010
1 Oct 10    31 Dec 2010
1 Jan 09    31 Dec 2009
1 Jan 10    31 Dec 2010

The above contains for each month, quarter and year of 2009, 2010.

I have another table with the following:

table2

start       end
15-12-09    31-12-09
15-01-12    31-12-13
01-01-11    31-12-13
30-01-98    31-12-13
01-01-98    31-12-13
01-01-98    31-12-13
23-12-12    31-12-13
12-11-11    31-12-13
01-01-10    31-12-13

For each entry in table2, I need to find the possible timeframes it falls into table1.

For ex. from table2, first entry -

15-12-09    31-12-09

falls to:

1 Dec 09    31 Dec 2009
1 Oct 09    31 Dec 2009
1 Jan 09    31 Dec 2009

Is it possible in Oracle SQL to identify it?

Answer 1

You must first define what do you mean with falls in table1 interval

There are general two possible interpretation. A more restrictive is the SUBINTERVAL, i.e. the matched interval is completely covered with the reference interval.

 match              <----------> 
 reference    <------------------>

Other more relaxed possibility is INTERSECT that means there is at least one point common to the both intervals.

 match                       <----------> 
 reference   <------------------>

Dependent on that decision you use a different join condition. In the query below the first possibility is implemented, simple swap the comments to get the other option.

Note that tables with your simulated data are created below.

select 
   tab2.start_d match_start, tab2.end_d match_end, 
   tab.start_d ref_start, tab.end_d ref_end 
from tab2 
join tab
-- option SUBINTERVAL
on tab.start_d <= tab2.start_d and tab2.end_d <= tab.end_d
-- option INTERSEC
--  on NOT (tab2.end_d <  tab.start_d OR tab2.start_d > tab.end_d)
order by 1,2,3;

result for the SUBINTERVAL option

MATCH_START       MATCH_END         REF_START         REF_END         
----------------- ----------------- ----------------- -----------------
15.12.09 00:00:00 31.12.09 00:00:00 01.01.09 00:00:00 31.12.09 00:00:00 
15.12.09 00:00:00 31.12.09 00:00:00 01.10.09 00:00:00 31.12.09 00:00:00 
15.12.09 00:00:00 31.12.09 00:00:00 01.12.09 00:00:00 31.12.09 00:00:00  

You'll get far more records for the INTERSECT option.

And here the test data

create table tab as
with tab as (
-- reference intervals
-- months
select add_months(to_date('01012009','ddmmyyyy'),rownum-1) start_d,
add_months(to_date('01012009','ddmmyyyy'),rownum)-1 end_d from dual connect by level <=24
union all
-- quartals
select add_months(to_date('01012009','ddmmyyyy'),3*(rownum-1)) start_d,
add_months(to_date('01012009','ddmmyyyy'),3*rownum)-1 end_d from dual connect by level <=24/3
union all
-- years
select add_months(to_date('01012009','ddmmyyyy'),12*(rownum-1)) start_d,
add_months(to_date('01012009','ddmmyyyy'),12*rownum)-1 end_d from dual connect by level <=24/12
) 
select * from tab;

create table tab2 as
with tab2 as (
-- matched intervals
select to_date('15-12-09','dd-mm-rr') start_d,     to_date('31-12-09','dd-mm-rr') end_d from dual union all
select to_date('15-01-12','dd-mm-rr') start_d,     to_date('31-12-13','dd-mm-rr') end_d from dual union all
select to_date('15-01-98','dd-mm-rr') start_d,     to_date('31-12-13','dd-mm-rr') end_d from dual
)
select * from tab2;