按日期间隔查找重复项

Question

我在下表中有前3列，我想获得第4列(冲突)。如果在按帐户划分的分区中，开始日期和结束日期之间的时间冲突，我想将其标记为'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

Answer 1

您可以使用:

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

对于样本数据:

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;

输出:

<表类>账户开始结束冲突tbody><<tr>130-SEP-2128-OCT-21没有121-JUL-2120-AUG-21没有230-JUL-2128-OCT-21对201-JUL-2120-AUG-21对