Getting the number of days before a status change

ojmp2001 ojmp2001 121 Reputation points

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

To the following

0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,441 Reputation points
    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;  


    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful