Romeo Romeo - 1 month ago 9
SQL Question

SQL count how many times status appear in succession

I have a table with list of cases, and a list of status that they have:

Case ID | Date | Status
------ | ---------- |-------
x | 2016-11-01 | Open
x | 2016-11-03 | Hold
x | 2016-11-04 | Hold
x | 2016-11-05 | Open
x | 2016-11-08 | Hold
x | 2016-11-15 | Processing
x | 2016-11-16 | Done


I want to indicate that there is a new record but status for the same case remained the same:

Case ID | Date | Status | count
------ | ---------- |---------- |------
x | 2016-11-01 | Open | 1
x | 2016-11-03 | Hold | 1
x | 2016-11-04 | Hold | 2
x | 2016-11-05 | Open | 1
x | 2016-11-08 | Hold | 1
x | 2016-11-15 | Processing | 1
x | 2016-11-16 | Done | 1


If I do something like this:

SELECT *
FROM
(SELECT
ROW_NUMBER() OVER (partition by [Case ID], [status] ORDER BY Date) AS Row,
ID, Status, Date
FROM
Cases) AS C
WHERE
ID = x
ORDER BY
Date


it doesn't take into account that the last 'Hold' was after the 'Open' and puts number 3 though I need number 1.

Answer

You need to detect islands of successive records having the same status value and consume the 'island id' in the PARTITION BY clause of a ROW_NUMBER window function:

SELECT  [Case ID], [Date], [status],
        ROW_NUMBER() OVER (PARTITION BY [Case ID], [status], grp ORDER BY [Date]) AS [count]
FROM (
   SELECT  [Case ID], [Date], [status], 
           ROW_NUMBER() OVER (PARTITION BY [Case ID] ORDER BY [Date]) -
           ROW_NUMBER() OVER (PARTITION BY [Case ID], [status] ORDER BY [Date]) AS grp   
   FROM Cases) AS t
ORDER BY [Date]