Find overlapping date in SQL-CodePudding

I need SELECT for finding data with overlapping date in Oracle SQL just from today to exactly one year ago. ID_FORMULAR is not UNIQUE value and I need to include just data with overlapping date where ID_FORMULAR is UNIQUE.

My code:

SELECT T1.*
  FROM VISITORS T1, VISITORS T2
 WHERE    (    T1.ID_FORMULAR != T2.ID_FORMULAR
           AND t1.FROM_DATE >= t2.FROM_DATE
           AND t1.FROM_DATE <= t2.TO_DATE
           AND T1.CREATED_DATE >= ADD_MONTHS (TRUNC (CURRENT_DATE), -12)
           AND T1.CREATED_DATE < TRUNC (CURRENT_DATE)   1)
       OR (    T1.ID_FORMULAR != T2.ID_FORMULAR
           AND t1.TO_DATE >= t2.FROM_DATE
           AND t1.TO_DATE <= t2.TO_DATE
           AND T1.CREATED_DATE >= ADD_MONTHS (TRUNC (CURRENT_DATE), -12)
           AND T1.CREATED_DATE < TRUNC (CURRENT_DATE)   1)
       OR (    T1.ID_FORMULAR != T2.ID_FORMULAR
           AND t1.TO_DATE >= t2.TO_DATE
           AND t1.FROM_DATE <= t2.FROM_DATE
           AND T1.CREATED_DATE >= ADD_MONTHS (TRUNC (CURRENT_DATE), -12)
           AND T1.CREATED_DATE < TRUNC (CURRENT_DATE)   1)

It is not working correctly. Any help?

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   (
  SELECT *
  FROM   visitors
  WHERE  created_date >= ADD_MONTHS(TRUNC(CURRENT_DATE), -12)
  AND    created_date <  TRUNC(CURRENT_DATE)   1
)
MATCH_RECOGNIZE(
  ORDER BY from_date
  ALL ROWS PER MATCH
  PATTERN (any_row overlap )
  DEFINE
    overlap AS  PREV(id_formular) != id_formular
            AND PREV(to_date)     >= from_date
)

Which, for the sample data:

CREATE TABLE visitors (id_formular, created_date, from_date, to_date) AS
SELECT 1, DATE '2022-08-01', DATE '2022-08-01', DATE '2022-08-03' FROM DUAL UNION ALL
SELECT 2, DATE '2022-08-01', DATE '2022-08-02', DATE '2022-08-04' FROM DUAL UNION ALL
SELECT 3, DATE '2022-08-01', DATE '2022-08-03', DATE '2022-08-05' FROM DUAL UNION ALL
SELECT 1, DATE '2022-08-01', DATE '2022-08-06', DATE '2022-08-06' FROM DUAL UNION ALL
SELECT 2, DATE '2022-08-01', DATE '2022-08-07', DATE '2022-08-09' FROM DUAL UNION ALL
SELECT 2, DATE '2022-08-01', DATE '2022-08-08', DATE '2022-08-10' FROM DUAL UNION ALL
SELECT 1, DATE '2022-08-01', DATE '2022-08-09', DATE '2022-08-11' FROM DUAL;

Outputs:

FROM_DATE ID_FORMULAR CREATED_DATE TO_DATE

01-AUG-22 1 01-AUG-22 03-AUG-22

02-AUG-22 2 01-AUG-22 04-AUG-22

03-AUG-22 3 01-AUG-22 05-AUG-22

08-AUG-22 2 01-AUG-22 10-AUG-22

09-AUG-22 1 01-AUG-22 11-AUG-22

FROM_DATE	ID_FORMULAR	CREATED_DATE	TO_DATE
01-AUG-22	1	01-AUG-22	03-AUG-22
02-AUG-22	2	01-AUG-22	04-AUG-22
03-AUG-22	3	01-AUG-22	05-AUG-22
08-AUG-22	2	01-AUG-22	10-AUG-22
09-AUG-22	1	01-AUG-22	11-AUG-22

db<>fiddle here

CodePudding user response：

I don't quite understand the question. The thing that is confusing me is that you need just rows where ID is unique. If ID is unique than there is no other row to overlap with. Anyway, lets suppose that the sample data is like below:

WITH
    tbl AS
        (
            SELECT 0 "ID",  DATE '2021-07-01' "CREATED", DATE '2021-07-01' "DATE_FROM", DATE '2021-07-13' "DATE_TO" FROM DUAL UNION ALL
            SELECT 1,       DATE '2021-12-01',           DATE '2021-12-01',             DATE '2021-12-03'           FROM DUAL UNION ALL
            SELECT 1,       DATE '2021-12-04',           DATE '2021-12-04',             DATE '2021-12-14'           FROM DUAL UNION ALL
            SELECT 1,       DATE '2021-12-12',           DATE '2021-12-12',             DATE '2021-12-29'           FROM DUAL UNION ALL
            SELECT 2,       DATE '2022-08-04',           DATE '2022-08-04',             DATE '2022-08-10'           FROM DUAL UNION ALL
            SELECT 2,       DATE '2022-08-11',           DATE '2022-08-11',             DATE '2022-08-21'           FROM DUAL UNION ALL
            SELECT 2,       DATE '2022-08-21',           DATE '2022-08-21',             DATE '2022-08-29'           FROM DUAL UNION ALL
            SELECT 3,       DATE '2022-08-11',           DATE '2022-08-11',             DATE '2022-08-29'           FROM DUAL UNION ALL
            SELECT 4,       DATE '2022-08-14',           DATE '2022-08-14',             DATE '2022-08-14'           FROM DUAL UNION ALL
            SELECT 4,       DATE '2022-08-29',           DATE '2022-08-14',             DATE '2022-08-29'           FROM DUAL 
        )

We can add some columns that will tell us if the ID is unique or not, what is the order of appearance of the same ID, what is the end date of the previous row for the same ID and if the rows of a particular ID overlaps or not. Here is the code: (used analytic functions with windowing clause)

SELECT 
    ID "ID",
    CASE WHEN Count(*) OVER (PARTITION BY ID ORDER BY ID) = 1 THEN 'Y' ELSE 'N' END "IS_UNIQUE",
    Count(ID) OVER (PARTITION BY ID ORDER BY ID, DATE_FROM, DATE_TO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ID_ORDER_NO",
    CREATED "CREATED",
    DATE_FROM "DATE_FROM",
    DATE_TO "DATE_TO",
    CASE 
        WHEN Count(ID) OVER (PARTITION BY ID ORDER BY ID, DATE_FROM, DATE_TO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 1 
        THEN Null 
    ELSE
        First_Value(DATE_TO) OVER (PARTITION BY ID ORDER BY ID, DATE_FROM, DATE_TO ROWS BETWEEN 1 PRECEDING  AND CURRENT ROW )
    END "PREVIOUS_END_DATE",
    CASE 
        WHEN Count(ID) OVER (PARTITION BY ID ORDER BY ID, DATE_FROM, DATE_TO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 1 
        THEN 'N' 
    ELSE
        CASE
            WHEN DATE_FROM <= First_Value(DATE_TO) OVER (PARTITION BY ID ORDER BY ID, DATE_FROM, DATE_TO ROWS BETWEEN 1 PRECEDING  AND CURRENT ROW )
            THEN 'Y'
        ELSE  'N'
        END
    END "OVERLAPS"
FROM
    TBL
WHERE
    CREATED BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'dd'), -12) And TRUNC(SYSDATE, 'dd')

Here is the resulting dataset...

/*   R e s u l t
        ID IS_UNIQUE ID_ORDER_NO CREATED   DATE_FROM DATE_TO   PREVIOUS_END_DATE OVERLAPS
---------- --------- ----------- --------- --------- --------- ----------------- --------
         1 N                   1 01-DEC-21 01-DEC-21 03-DEC-21                   N        
         1 N                   2 04-DEC-21 04-DEC-21 14-DEC-21 03-DEC-21         N        
         1 N                   3 12-DEC-21 12-DEC-21 29-DEC-21 14-DEC-21         Y        
         2 N                   1 04-AUG-22 04-AUG-22 10-AUG-22                   N        
         2 N                   2 11-AUG-22 11-AUG-22 21-AUG-22 10-AUG-22         N        
         2 N                   3 21-AUG-22 21-AUG-22 29-AUG-22 21-AUG-22         Y        
         3 Y                   1 11-AUG-22 11-AUG-22 29-AUG-22                   N        
         4 N                   1 14-AUG-22 14-AUG-22 14-AUG-22                   N        
         4 N                   2 29-AUG-22 14-AUG-22 29-AUG-22 14-AUG-22         Y
*/

This dataset could be further used to get you the rows and columns that you are trying to get. You can filter it, do some other calculations (like number of overlaping days), get number of rows per ID and so on....
Regards...