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

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:

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

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.

CodePudding user response：

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

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

CodePudding user response：

I was going to post a match_recognize approach, but got distracted and MTO beat me to it by a long way...

But just for fun, another approach is just to look for any date with nothing immediately after it:

select account_number, dt as end_date
from tablename t1
where not exists (
  select null
  from tablename t2
  where t2.account_number = t1.account_number
  and t2.dt = t1.dt   1
)
order by account_number, dt;

db<>fiddle