By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE /*HR.*/PROJECT (
TASK_ID NUMBER NOT NULL ENABLE,
START_DATE DATE,
END_DATE DATE,
CONSTRAINT CITI_PK PRIMARY KEY (TASK_ID)
);
Insert into /*HR.*/PROJECT (TASK_ID,START_DATE,END_DATE)
SELECT 1, DATE'2021-11-01', DATE '2021-11-02' FROM DUAL UNION ALL
SELECT 2, DATE'2021-11-02', DATE '2021-11-03' FROM DUAL UNION ALL
SELECT 3, DATE'2021-11-03', DATE '2021-11-04' FROM DUAL UNION ALL
SELECT 4, DATE'2021-11-13', DATE '2021-11-14' FROM DUAL UNION ALL
SELECT 5, DATE'2021-11-14', DATE '2021-11-15' FROM DUAL UNION ALL
SELECT 6, DATE'2021-11-28', DATE '2021-11-29' FROM DUAL UNION ALL
SELECT 7, DATE'2021-11-30', DATE '2021-12-01' FROM DUAL;
7 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
SELECT start_date,
end_date,
end_date - start_date AS days_to_complete
FROM /*HR.*/project
MATCH_RECOGNIZE(
ORDER BY start_date
MEASURES
FIRST(start_date) AS start_date,
LAST(end_date) AS end_date
PATTERN (first_date successive_dates*)
DEFINE
successive_dates AS PREV(end_date) = start_date
)
ORDER BY start_date DESC
| START_DATE | END_DATE | DAYS_TO_COMPLETE |
|---|---|---|
| 2021-11-30 00:00:00 | 2021-12-01 00:00:00 | 1 |
| 2021-11-28 00:00:00 | 2021-11-29 00:00:00 | 1 |
| 2021-11-13 00:00:00 | 2021-11-15 00:00:00 | 2 |
| 2021-11-01 00:00:00 | 2021-11-04 00:00:00 | 3 |