question

ojmp2001ojmp2001-0652 avatar image
0 Votes"
ojmp2001ojmp2001-0652 asked ojmp2001ojmp2001-0652 commented

Getting the number of days before a status change

How do I get the number of days between 2 dates before a status change. There are times a status could flip back in the the future.
Original table
133613-image.png


To the following
133640-image.png


sql-server-transact-sql
image.png (10.4 KiB)
image.png (8.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TomCooper-6989 avatar image
1 Vote"
TomCooper-6989 answered ojmp2001ojmp2001-0652 commented
 Declare @Sample Table(ID int, Date date, Status varchar(10));
 Insert @Sample(ID, Date, Status) Values
 (1, '1/1/2020', 'abc'),
 (1, '1/2/2020', 'abc'),
 (1, '1/3/2020', 'abc'),
 (1, '1/4/2020', 'def'),
 (1, '1/5/2020', 'def'),
 (1, '1/6/2020', 'abc'),
 (1, '1/7/2020', 'abc'),
 (1, '1/8/2020', 'xyz'),
 (1, '1/9/2020', 'xyz'),
 (1, '1/10/2020', 'xyz');
 ;With cte As
 (Select ID, Date, Status,
   Row_Number() Over(Partition By ID Order By Date) - Row_Number() Over(Partition By ID, Status Order By Date) As Island
 From @Sample)
 Select ID, Min(Date) As FirstDate, Max(Date) As LastDate, 
   Status, DateDiff(day, Min(Date), Max(Date)) As NumberOfDays
 From cte
 Group By ID, Status, Island
 Order By ID, FirstDate;

Tom

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank u, that worked

0 Votes 0 ·