2

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.

Improve this question
1
  • 1
    Those date/time columns need to be DATE data types. (DATEs do not have a format).Also, if you can provide the sample data as a CTE (or CREATE TABLE and INSERT statements), it will be easier to build and test potential solutions.
    – Michael Kutz
    Commented Jun 7, 2020 at 1:52

2 Answers 2

Reset to default
2 
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
Improve this answer
1
  • Thank you, @balazs-papp Also I would like to share the solutions proposed here
    – Vitor Mira
    Commented Jun 12, 2020 at 1:38
3

A method that does not require any self-joins and will only read the table once.

This is adapted from this answer which contains an explanation of the code. All that has changed is to add PARTITION BY order_id to calculate the date ranges for each order_id and then to return the ranges (rather than total the values, as per the linked answer):

SELECT order_id,
       start_date_time,
       end_date_time
FROM   (
  SELECT order_id,
         LAG( dt ) OVER ( PARTITION BY order_id ORDER BY dt ) AS start_date_time,
         dt AS end_date_time,
         start_end
  FROM   (
    SELECT order_id,
           dt,
           CASE SUM( value ) OVER ( PARTITION BY order_id ORDER BY dt ASC, value DESC, ROWNUM ) * value
             WHEN 1 THEN 'start'
             WHEN 0 THEN 'end'
           END AS start_end
    FROM   table_name
    UNPIVOT ( dt FOR value IN ( start_date_time AS 1, end_date_time AS -1 ) )
  )
  WHERE start_end IS NOT NULL
)
WHERE  start_end = 'end';

From Oracle 12, you can use MATCH_RECOGNIZE to do row-by-row processing:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY order_id
  ORDER     BY start_date_time
  MEASURES
    FIRST(start_date_time) AS start_date_time,
    MAX(end_date_time)     AS end_date_time
  ONE ROW PER MATCH
  PATTERN (overlapping_rows* last_row)
  DEFINE
    overlapping_rows AS NEXT(start_date_time) <= MAX(end_date_time)
)

Which, for your test data:

CREATE TABLE table_name (
  order_id NUMBER,
  start_date_time DATE,
  end_date_time DATE
);

INSERT INTO table_name ( order_id, start_date_time, end_date_time )
SELECT 3933, TIMESTAMP '2020-02-04 08:00:00', TIMESTAMP '2020-02-04 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 13:30:00', TIMESTAMP '2020-02-04 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 14:00:00', TIMESTAMP '2020-02-04 19:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 13:40:12', TIMESTAMP '2020-02-05 14:34:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:00:00', TIMESTAMP '2020-02-05 18:55:12' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:49:48', TIMESTAMP '2020-02-05 15:04:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 08:00:00', TIMESTAMP '2020-02-06 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 13:30:00', TIMESTAMP '2020-02-06 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 14:10:12', TIMESTAMP '2020-02-06 18:49:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 10:30:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 13:30:00', TIMESTAMP '2020-02-07 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 15:00:00', TIMESTAMP '2020-05-14 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 08:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 09:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 10:15:00', TIMESTAMP '2020-05-15 12:15:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 13:19:48', TIMESTAMP '2020-05-15 16:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 08:49:48', TIMESTAMP '2020-05-18 09:45:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 16:58:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 15:34:48', TIMESTAMP '2020-05-18 16:10:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 16:30:00', TIMESTAMP '2020-05-18 16:45:00' FROM DUAL;

Both queries output:

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

db<>fiddle here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.