find overlap between two sets of date ranges

Question

I would like to find the overlap between two sets of date ranges for every order number using Oracle SQL or PL/SQL.

The inputs are "result set one" and "result set two". The output should be "overlap".

result set one

WITH T_RESULT_SET_ONE as(
select 21365 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 10:30:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21365 order_number,to_date('02/01/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 18:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21367 order_number,to_date('01/01/2021 08:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:43:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21367 order_number,to_date('01/01/2021 16:34:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 18:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
union
select 21367 order_number,to_date('04/01/2021 15:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('04/01/2021 16:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
)

or

result set two

T_RESULT_SET_TWO as(
select 21365 order_number,to_date('01/01/2021 09:30:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:45:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21365 order_number,to_date('02/01/2021 13:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 17:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
union
select 21367 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 10:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21367 order_number,to_date('01/01/2021 16:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21367 order_number,to_date('05/01/2021 19:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('04/01/2021 19:46:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
)

or

overlap

T_OVERLAP as
(
select 21365 order_number,to_date('01/01/2021 09:30:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:45:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
union
select 21365 order_number,to_date('02/01/2021 14:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('02/01/2021 17:00:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
union
select 21367 order_number,to_date('01/01/2021 09:00:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 09:43:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
UNION
select 21367 order_number,to_date('01/01/2021 16:34:00', 'DD/MM/YYYY HH24:MI:SS') start_date_time, to_date('01/01/2021 18:15:00', 'DD/MM/YYYY HH24:MI:SS') finish_date_time FROM DUAL
)

or

The following image ilustrates the operation I am trying to execute (the date ranges are not the same as the ones I provided earlier)

Could anyone provide a SQL query or PL/SQL program that does that?

Answer 1

It seems what you are looking for is not INTERSECT but overlaps. In Oracle intersect generally refers to the common result of 2 queries:

Select <columns list> from table1
INTERSECT
Select <columns list> from table2;

Where the column lists have the same definition and the resulting values are the same. What you are looking for is where the values overlap one another each other not where the rows contain same values.
Lets consider 2 events call then 'A' and 'B', there are 4 possibilities for overlap:

1. A starts, B starts, B ends, A ends. A completely overlaps B.
2. A starts, B starts, A ends, B ends. A overlaps beginning of B
3. B starts, A starts, B ends, A ends. A overlaps ending of B
4. B starts, A starts, A ends, B ends. A is completely overlap by B.

Resolving is just determining is needs to determine the overlap we take the greatest start time and the least end time. With the data you provided this requires just one of the above:

select order_number
     , greatest(t1start, t2start) start_date_time
     , least(t1finish,t2finish) finish_date_time     
 from ( select t1.order_number
             , t1.start_date_time   t1start
             , t1.finish_date_time  t1finish
             , t2.start_date_time   t2start
             , t2.finish_date_time  t2finish             
          from t_result_set_one t1
          join t_result_set_two t2
            on t1.order_number = t2.order_number 
         where (    t1.finish_date_time >= t2.start_date_time
                and t1.start_date_time  <= t2.finish_date_time
               ) 
      );    

See fiddle here. I leave the other 3 possibilities for you.