By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (ID, Begin_Date, End_Date) AS
SELECT 1, DATE '2023-01-01', DATE '2023-02-01' FROM DUAL UNION ALL
SELECT 1, DATE '2023-02-02', DATE '2023-02-15' FROM DUAL UNION ALL
SELECT 1, DATE '2023-02-20', DATE '2023-09-21' FROM DUAL UNION ALL
SELECT 1, DATE '2023-09-22', DATE '2023-10-11' FROM DUAL UNION ALL
SELECT 2, DATE '2023-01-11', DATE '2023-04-11' FROM DUAL UNION ALL
SELECT 2, DATE '2023-05-09', DATE '2023-06-15' FROM DUAL UNION ALL
SELECT 2, DATE '2023-07-07', DATE '2023-09-17' FROM DUAL;
7 rows affected
SELECT *
FROM table_name
MATCH_RECOGNIZE (
PARTITION BY id
ORDER BY begin_date
MEASURES
MATCH_NUMBER() AS grp
ALL ROWS PER MATCH
PATTERN (consecutive_dates* last_date)
DEFINE consecutive_dates AS end_date + INTERVAL '1' DAY = NEXT(begin_date)
)
| ID | BEGIN_DATE | GRP | END_DATE |
|---|---|---|---|
| 1 | 2023-01-01 00:00:00 | 1 | 2023-02-01 00:00:00 |
| 1 | 2023-02-02 00:00:00 | 1 | 2023-02-15 00:00:00 |
| 1 | 2023-02-20 00:00:00 | 2 | 2023-09-21 00:00:00 |
| 1 | 2023-09-22 00:00:00 | 2 | 2023-10-11 00:00:00 |
| 2 | 2023-01-11 00:00:00 | 1 | 2023-04-11 00:00:00 |
| 2 | 2023-05-09 00:00:00 | 2 | 2023-06-15 00:00:00 |
| 2 | 2023-07-07 00:00:00 | 3 | 2023-09-17 00:00:00 |