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

Answer 1

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.