Oracle PL/SQL – How to Merge Datetime Ranges

oracleoracle-11g-r2plsqlstored-procedures

I have been struggling to merge datetime ranges in oracle SQL/PLSQL (Database Standard Edition 11gR2).

I am trying to merge datetime ranges so that the following data

order_id    start_date_time         end_date_time
3933        04/02/2020 08:00:00     04/02/2020 12:00:00
3933        04/02/2020 13:30:00     04/02/2020 17:00:00
3933        04/02/2020 14:00:00     04/02/2020 19:00:00
3933        05/02/2020 13:40:12     05/02/2020 14:34:48
3933        05/02/2020 14:00:00     05/02/2020 18:55:12
3933        05/02/2020 14:49:48     05/02/2020 15:04:48
3933        06/02/2020 08:00:00     06/02/2020 12:00:00
3933        06/02/2020 13:30:00     06/02/2020 17:00:00
3933        06/02/2020 14:10:12     06/02/2020 18:49:48
3933        07/02/2020 08:00:00     07/02/2020 10:30:00
3933        07/02/2020 08:00:00     07/02/2020 12:00:00
3933        07/02/2020 13:30:00     07/02/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       14/05/2020 15:00:00     14/05/2020 16:30:00
11919       15/05/2020 08:40:12     15/05/2020 16:30:00
11919       15/05/2020 09:40:12     15/05/2020 16:30:00
11919       15/05/2020 10:15:00     15/05/2020 12:15:00
11919       15/05/2020 13:19:48     15/05/2020 16:00:00
11919       18/05/2020 08:49:48     18/05/2020 09:45:00
11919       18/05/2020 10:00:00     18/05/2020 17:00:00
11919       18/05/2020 10:00:00     18/05/2020 16:58:12
11919       18/05/2020 15:34:48     18/05/2020 16:10:12
11919       18/05/2020 16:30:00     18/05/2020 16:45:00
...         ...                     ...

would transform into the following result set

--after merge (this is the result I am seeking)
order_id    start_date_time         end_date_time
3933        04/02/2020 08:00:00     04/02/2020 12:00:00
3933        04/02/2020 13:30:00     04/02/2020 19:00:00
3933        05/02/2020 13:40:12     05/02/2020 18:55:12
3933        06/02/2020 08:00:00     06/02/2020 12:00:00
3933        06/02/2020 13:30:00     06/02/2020 18:49:48
3933        07/02/2020 08:00:00     07/02/2020 12:00:00
3933        07/02/2020 13:30:00     07/02/2020 17:00:00
11919       14/05/2020 09:00:00     14/05/2020 17:00:00
11919       15/05/2020 08:40:12     15/05/2020 16:30:00
11919       18/05/2020 08:49:48     18/05/2020 17:00:00
...         ...                     ...

The format of start_date_time and end_date_time is DAY/MONTH/YEAR HH24:MI:SS.

Any suggestion/solution on how to make that merge in Oracle SQL or PL/SQL?

I thought that was a trivial problem, however I was not able to find a solution on the internet yet.

Thanks in advance.

Best Answer

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
create table data(order_id number, start_date_time date, end_date_time date);
insert into data values (3933  ,'04/02/2020 08:00:00', '04/02/2020 12:00:00');
insert into data values (3933  ,'04/02/2020 13:30:00', '04/02/2020 17:00:00');
insert into data values (3933  ,'04/02/2020 14:00:00', '04/02/2020 19:00:00');
insert into data values (3933  ,'05/02/2020 13:40:12', '05/02/2020 14:34:48');
insert into data values (3933  ,'05/02/2020 14:00:00', '05/02/2020 18:55:12');
insert into data values (3933  ,'05/02/2020 14:49:48', '05/02/2020 15:04:48');
insert into data values (3933  ,'06/02/2020 08:00:00', '06/02/2020 12:00:00');
insert into data values (3933  ,'06/02/2020 13:30:00', '06/02/2020 17:00:00');
insert into data values (3933  ,'06/02/2020 14:10:12', '06/02/2020 18:49:48');
insert into data values (3933  ,'07/02/2020 08:00:00', '07/02/2020 10:30:00');
insert into data values (3933  ,'07/02/2020 08:00:00', '07/02/2020 12:00:00');
insert into data values (3933  ,'07/02/2020 13:30:00', '07/02/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 09:00:00', '14/05/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 09:00:00', '14/05/2020 17:00:00');
insert into data values (11919 ,'14/05/2020 15:00:00', '14/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 08:40:12', '15/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 09:40:12', '15/05/2020 16:30:00');
insert into data values (11919 ,'15/05/2020 10:15:00', '15/05/2020 12:15:00');
insert into data values (11919 ,'15/05/2020 13:19:48', '15/05/2020 16:00:00');
insert into data values (11919 ,'18/05/2020 08:49:48', '18/05/2020 09:45:00');
insert into data values (11919 ,'18/05/2020 10:00:00', '18/05/2020 17:00:00');
insert into data values (11919 ,'18/05/2020 10:00:00', '18/05/2020 16:58:12');
insert into data values (11919 ,'18/05/2020 15:34:48', '18/05/2020 16:10:12');
insert into data values (11919 ,'18/05/2020 16:30:00', '18/05/2020 16:45:00');
commit;

Then with some magic:

select d1.order_id, d1.start_date_time, max(d2.end_date_time) as end_date_time
from data d1 join data d2
on (d1.order_id = d2.order_id and
(d1.start_date_time <= d2.end_date_time and d2.start_date_time <= d1.end_date_time))
group by d1.order_id, d1.start_date_time
intersect
select d1.order_id, min(d1.start_date_time), d2.end_date_time
from data d1 join data d2
on (d1.order_id = d2.order_id and
(d1.start_date_time <= d2.end_date_time and d2.start_date_time <= d1.end_date_time))
group by d1.order_id, d2.end_date_time
order by 1, 2, 3;

  ORDER_ID START_DATE_TIME     END_DATE_TIME      
---------- ------------------- -------------------
      3933 04/02/2020 08:00:00 04/02/2020 12:00:00
      3933 04/02/2020 13:30:00 04/02/2020 19:00:00
      3933 05/02/2020 13:40:12 05/02/2020 18:55:12
      3933 06/02/2020 08:00:00 06/02/2020 12:00:00
      3933 06/02/2020 13:30:00 06/02/2020 18:49:48
      3933 07/02/2020 08:00:00 07/02/2020 12:00:00
      3933 07/02/2020 13:30:00 07/02/2020 17:00:00
     11919 14/05/2020 09:00:00 14/05/2020 17:00:00
     11919 15/05/2020 08:40:12 15/05/2020 16:30:00
     11919 18/05/2020 08:49:48 18/05/2020 09:45:00
     11919 18/05/2020 10:00:00 18/05/2020 17:00:00
Related Question