Select start and end dates for changing values in SQL

Question

I have a database with accounts and historical status changes

select Date, Account, OldStatus, NewStatus from HistoricalCodes order by Account, Date

Date	Account	OldStatus	NewStatus
2020-01-01	12345	1	2
2020-10-01	12345	2	3
2020-11-01	12345	3	2
2020-12-01	12345	2	1
2020-01-01	54321	2	3
2020-09-01	54321	3	2
2020-12-01	54321	2	3

For every account I need to determine Start Date and End Date when Status = 2. An additional challenge is that the status can change back and forth multiple times. Is there a way in SQL to create something like this for at least first two timeframes when account was in 2? Any ideas?

Account	StartDt_1	EndDt_1	StartDt_2	EndDt_2
12345	2020-01-01	2020-10-01	2020-11-01	2020-12-01
54321	2020-09-01	2020-12-01

score 0 · Accepted Answer · answered Dec 15 '20 at 22:06

I would suggest putting this information in separate rows:

select t.*
from (select account, date as startdate,
             lead(date) over (partition by account order by date) as enddate
      from t
     ) t
where newstatus = 2;

This produces a separate row for each period when an account has a status of 2. This is better than putting the dates in separate pairs of columns, because you do not need to know the maximum number of periods of status = 2 when you write the query.

score 0 · Answer 2 · answered Dec 16 '20 at 00:00

For a fixed maximum of status changes per account, you can use window functions and conditional aggregation:

select account,
    max(case when rn = 1 then      date end) as start_dt1,
    max(case when rn = 1 then lead_date end) as end_dt1,
    max(case when rn = 2 then      date end) as start_dt2,
    max(case when rn = 2 then lead_date end) as end_dt2
from (
    select t.*, 
        row_number() over(partition by account, newstatus order by date) as rn,
        lead(date) over(partition by account order by date) as lead_date
    from mytable t
) t
where newstatus = 2
group by account

You can extend the select clause with more conditional expressions to handle more possible ranges per account.

Select start and end dates for changing values in SQL

2 Answers2