By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE GLOBAL TEMPORARY TABLE my_gtt
(
id NUMBER(10),
code_id NUMBER(10),
code NUMBER(10),
date_from DATE,
date_to DATE
)
ON COMMIT PRESERVE ROWS;
BEGIN
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-01', DATE '2022-01-10');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-02', DATE '2022-01-04');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-06', DATE '2022-01-11');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-13', DATE '2022-01-16');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-14', DATE '2022-01-15');
END;
/
1 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD Hh24:MI:SS';
SELECT id,
MIN(dt) AS date_from,
MAX(dt) AS date_to
FROM (
SELECT id,
dt,
SUM(value) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS match_no
FROM (
SELECT id,
dt,
type * SUM(type) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS value
FROM my_gtt
UNPIVOT (dt FOR type IN (date_from AS 1, date_to AS -1))
)
WHERE value IN (1,0)
)
GROUP BY id, match_no
| ID | DATE_FROM | DATE_TO |
|---|---|---|
| 10 | 2022-02-02 00:00:00 | 2022-02-04 21:37:00 |
| 10 | 2022-02-07 01:00:00 | 2022-02-08 00:00:00 |
| 20 | 2022-02-01 05:00:00 | 2022-02-03 00:00:00 |
| 30 | 2022-02-02 00:00:00 | 2022-02-04 00:00:00 |
| 40 | 2022-02-07 03:00:00 | 2022-02-08 23:10:00 |
| 50 | 2022-02-04 00:00:00 | 2022-02-05 23:31:00 |
| 60 | 2022-01-01 00:00:00 | 2022-01-11 00:00:00 |
| 60 | 2022-01-13 00:00:00 | 2022-01-16 00:00:00 |
SELECT *
FROM my_gtt
MATCH_RECOGNIZE (
PARTITION BY id
ORDER BY date_from, date_to
MEASURES
MIN(date_from) AS start_date,
MAX(date_to) AS end_date
PATTERN (overlap* last_row)
DEFINE
overlap AS MAX(date_to) >= NEXT(date_from)
);
| ID | START_DATE | END_DATE |
|---|---|---|
| 10 | 2022-02-02 00:00:00 | 2022-02-04 21:37:00 |
| 10 | 2022-02-07 01:00:00 | 2022-02-08 00:00:00 |
| 20 | 2022-02-01 05:00:00 | 2022-02-03 00:00:00 |
| 30 | 2022-02-02 00:00:00 | 2022-02-04 00:00:00 |
| 40 | 2022-02-07 03:00:00 | 2022-02-08 23:10:00 |
| 50 | 2022-02-04 00:00:00 | 2022-02-05 23:31:00 |
| 60 | 2022-01-01 00:00:00 | 2022-01-11 00:00:00 |
| 60 | 2022-01-13 00:00:00 | 2022-01-16 00:00:00 |