Extract the status start date and change date for a given status code Teradata

Question

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

Answer 1

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