Find duplicates by date interval-CodePudding

I have the first 3 columns in the below table and I would like to obtaing the 4th one (CLASHING). If, over partition by account, the period between start and end dates is clashing, I would like to mark it as 'X'.

ACCOUNT     START         END   CLASHING
----------------------------------------
      1 30-Sep-21   28-Oct-21         NO
      1 01-Jul-21   20-Aug-21         NO
      2 30-Jul-21   28-Oct-21        YES
      2 01-Jul-21   20-Aug-21        YES

CodePudding user response：

You can use:

SELECT t.*,
       CASE
       WHEN EXISTS(SELECT 1
                   FROM   table_name x
                   WHERE  x."START" < t."END"
                   AND    x."END"   > t."START"
                   AND    x.account = t.account
                   AND    x.ROWID   != t.ROWID)
       THEN 'YES'
       ELSE 'NO'
       END AS clashing
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (ACCOUNT, "START", "END") AS
SELECT 1, DATE '2021-09-30', DATE '2021-10-28' FROM DUAL UNION ALL
SELECT 1, DATE '2021-07-21', DATE '2021-08-20' FROM DUAL UNION ALL
SELECT 2, DATE '2021-07-30', DATE '2021-10-28' FROM DUAL UNION ALL
SELECT 2, DATE '2021-07-01', DATE '2021-08-20' FROM DUAL;

Outputs:

ACCOUNT START END CLASHING

1 30-SEP-21 28-OCT-21 NO

1 21-JUL-21 20-AUG-21 NO

2 30-JUL-21 28-OCT-21 YES

2 01-JUL-21 20-AUG-21 YES

ACCOUNT	START	END	CLASHING
1	30-SEP-21	28-OCT-21	NO
1	21-JUL-21	20-AUG-21	NO
2	30-JUL-21	28-OCT-21	YES
2	01-JUL-21	20-AUG-21	YES

db<>fiddle here