I have the data in the following format with several Status codes with their dates. The codes are in question are 3 and 8. I need to find the start date when STcode=3/8 and use it as STDATE and if STCODE changes to other than 3/8 then it would become my ENDDATE
In the following example, ID 101 went into STCODE=3 for the first time on 10/21/2022 and changed its status to 6 on 10/26/2022. Again on 10/26/2022 it was in STCODE=3 with a change date on 10/27/2022. I need to have these as two separate records in my output
ID STCODE DATE
101 3 10/21/2022
101 3 10/22/2022
101 3 10/23/2022
101 6 10/25/2022
101 3 10/26/2022
101 7 10/27/2022
102 8 10/25/2022
102 5 10/26/2022
Want
ID STDATE ENDDATE
101 10/21/2022 10/25/2022
101 10/26/2022 10/27/2022
102 10/25/2022 10/26/2022
I tried something like this but it is not yielding any desired results
WITH STS AS (
SELECT
ID,
STCODE,
DATE,
ROW_NUMBER() OVER (ORDER BY DATE) AS rn,
ROW_NUMBER() OVER (PARTITION BY STCODE ORDER BY DATE) AS str_rn
FROM
MyTable
)
SELECT
ID,
MIN(DATE) AS STDATE,
MAX(DATE) AS ENDDATE
FROM STS
WHERE
STCODE in (3,8)
GROUP BY ID
You'll want to make sure this handles all of your potential cases, but it works for the sample data and is the gist of a pretty standard approach.
Rationale - Use lead() to get the next status code and associated date. Then filter out any records whose status code stayed in a non-expiration triggering status. Once you do that you'll be left with only the first record in each status sequence with the appropriate enddate.
CREATE VOLATILE TABLE SampleData (
ID INTEGER,
STCODE INTEGER,
event_date DATE
) ON COMMIT PRESERVE ROWS;
-- Inserts
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 3, DATE '2022-10-21');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 3, DATE '2022-10-22');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 3, DATE '2022-10-23');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 6, DATE '2022-10-25');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 3, DATE '2022-10-26');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (101, 7, DATE '2022-10-27');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (102, 8, DATE '2022-10-25');
INSERT INTO SampleData (ID, STCODE, event_date) VALUES (102, 5, DATE '2022-10-26');
with
previous_stcodes as (
select sd.*,
lag(stcode) over ( partition by id order by event_date) as previous_stcode
from SampleData sd),
records_with_triggering_status_changes as (
select id,
stcode,
event_date,
lead(event_date) over ( partition by id order by event_date) as next_event_date
from previous_stcodes
where (stcode in (3,8) and coalesce(previous_stcode,-1) not in (3,8)) -- Changed to or originated as tagged status
or (stcode not in (3,8) and previous_stcode in (3,8)) -- Changed from non-tagged status, to tagged status
)
select id,
event_date,
next_event_date as end_date
from records_with_triggering_status_changes
where stcode in (3,8)
order
by id,
event_date