Search code examples
sqloracle-databasegaps-and-islands

Oracle SQL "Queue" Table: Every time an ID leaves the table

I have a "Queue" table in an Oracle sql database where IDs enter on a certain date, and have an entry/row every day they remain in the table until they leave. These IDs can return multiple times. Each time they have consecutive dates/rows until they leave.

The highlighted dates show when an ID exits the table:

enter image description here

I need a query that outputs every time an ID leaves:

enter image description here

I am working on the below query with the Split field serving as a minimum number of days to work around missing data over weekends or holidays. I don't want 5 or less days to trigger an "exit date" if the ID only leave the table for 1-5 days.

    SELECT 
    Account_Number
    , SPLIT
    , MIN(Dt) AS end_date
    from(
        SELECT Account_Number
        , Dt
        , LEAD (Dt) OVER (ORDER BY Account_Number,Dt) as next_dt
        , case when LEAD (Dt) OVER (ORDER BY Account_Number,Dt) - Dt > 5 then 1 else 0 end as DaysDiff
        , LEAD (Dt) OVER (ORDER BY Account_Number,Dt) - Dt as split
        from tablename
    )
    WHERE split >  5
    and Account_Number = '123'
    GROUP BY Account_Number, SPLIT

The issue is the query right now outputs only the first two dates. It misses the 3rd and final time the ID leaves the table.

Solution

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

    SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY account_number
  ORDER     BY dt
  MEASURES
    LAST(dt) AS leave_dt
  PATTERN (consecutive_dt* last_dt)
  DEFINE
    consecutive_dt AS NEXT(dt) <= dt + INTERVAL '5' DAY
);

    Which, for the sample data:

    CREATE TABLE table_name (account_number, dt) AS
SELECT 123, DATE '2022-02-18' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 11 UNION ALL
SELECT 123, DATE '2022-03-19' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 123, DATE '2022-04-16' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 18;

    Outputs:

    ACCOUNT_NUMBER LEAVE_DT
    123 2022-02-28 00:00:00
    123 2022-03-21 00:00:00
    123 2022-05-03 00:00:00

    db<>fiddle here