Eliminate overlapping date ranges

Question

My data in an Oracle table is like this. I need a solution in Oracle SQL

StDt	EdDt	User Stat
20-12-2021	12-06-2022	A
16-06-2022	31-12-4712	A
09-06-2022	30-06-2022	B

OUTPUT :-

StDt	EdDt
20-12-2021	31-12-4712

This output is because the person was active throughout the time till 31-12-4712.

Another Scenario :-

StDt	EdDt	User Stat
20-12-2021	31-12-4712	A
09-06-2022	30-06-2022	B

Output :-

StDt	EdDt
20-12-2021	31-12-4712

Another Scenario :-

StDt	EdDt	User Stat
20-12-2021	12-06-2022	A
16-06-2022	25-06-2022	A
20-06-2022	30-06-2022	B
10-10-2022	31-03-2023	B

Output :-

StDt	EdDt
20-12-2021	12-06-2022
16-06-2022	30-06-2022
10-10-2022	31-03-2022

So in short we have to remove the overlapping date range here.

Does this answer your question? [Merge overlapping date intervals](https://stackoverflow.com/questions/2561130/merge-overlapping-date-intervals) — Olivier Jacot-Descombes, Dec 13 '22 at 19:14
@OlivierJacot-Descombes That is an answer for SQL Server and the answers are all syntactically invalid in Oracle and if you tried migrating them to Oracle would be much less efficient than using Oracle specific functionality. — MT0, Dec 13 '22 at 21:27
@MT0, the [first solution](https://stackoverflow.com/a/8120432/880990) uses only standard SQL. — Olivier Jacot-Descombes, Dec 14 '22 at 12:50
@OlivierJacot-Descombes As I said, "if you tried migrating them to Oracle would be much less efficient than using Oracle specific functionality"; the answer you highlight may work but it is performing a self-join four times and is going to be very inefficient; in Oracle, you can use `MATCH_RECOGNIZE` and only query the table once but you cannot give that solution to that linked question because it is for the wrong RDBMS so perfectly valid (and better solutions) to this question would not be acceptable as answer to that question; hence that is not a good duplicate target. — MT0, Dec 14 '22 at 13:00

score 2 · Answer 1 · answered Dec 13 '22 at 19:16

This is a classical job for MATCH_RECOGNIZE, a general pattern:

MATCH_RECOGNIZE (
     PARTITION BY userstat
     ORDER BY stdt, eddt
     MEASURES FIRST(stdt) AS stdt, MAX(eddt) as eddt
     PATTERN( merged* start )
     DEFINE
        merged AS MAX(eddt) >= NEXT(stdt)
)

score 0 · Answer 2 · answered Dec 13 '22 at 21:19

You can use a MERGE statement with MATCH_RECOGNIZE:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         rn,
         MAX(eddt) OVER (PARTITION BY user_stat, mno) AS eddt
  FROM   table_name
  MATCH_RECOGNIZE(
    PARTITION BY user_stat
    ORDER BY StDt
    MEASURES
      COUNT(*) AS rn,
      MATCH_NUMBER() AS mno
    ALL ROWS PER MATCH
    PATTERN (overlapping* final_row)
    DEFINE
      overlapping AS MAX(eddt) >= NEXT(stdt)
  )
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE
  SET   eddt = src.eddt
  DELETE WHERE rn > 1;

Which, for the sample data:

CREATE TABLE table_name (StDt, EdDt, User_Stat) AS
SELECT DATE '2021-12-20', DATE '2022-06-12', 'A' FROM DUAL UNION ALL
SELECT DATE '2022-06-16', DATE '4712-12-31', 'A' FROM DUAL UNION ALL
SELECT DATE '2022-06-09', DATE '2022-06-30', 'B' FROM DUAL UNION ALL
SELECT DATE '2022-06-09', DATE '2022-06-30', 'C' FROM DUAL UNION ALL
SELECT DATE '2022-06-15', DATE '2022-06-20', 'C' FROM DUAL UNION ALL
SELECT DATE '2022-06-15', DATE '2022-06-20', 'D' FROM DUAL UNION ALL
SELECT DATE '2022-06-18', DATE '2022-06-23', 'D' FROM DUAL UNION ALL
SELECT DATE '2022-06-25', DATE '2022-06-30', 'D' FROM DUAL;

Then, after the MERGE statement the table contains:

STDT	EDDT	USER_STAT
2021-12-20 00:00:00	2022-06-12 00:00:00	A
2022-06-16 00:00:00	4712-12-31 00:00:00	A
2022-06-09 00:00:00	2022-06-30 00:00:00	B
2022-06-09 00:00:00	2022-06-30 00:00:00	C
2022-06-15 00:00:00	2022-06-23 00:00:00	D
2022-06-25 00:00:00	2022-06-30 00:00:00	D

fiddle

Eliminate overlapping date ranges

2 Answers2