T-SQL Reset Row number when field changes, including repeated instances

Question

I'm trying to find a way to show how many consecutive times an event types happens before a second event type happens. When I have that I need to show when an event type happens 6 times in a row

To simplify I have a 'Calls' table containing

CallID
UserID
Outcome
DateOfCall

From there I have numerous UserIDs with outcome as Yes or No at various times

I need to find when No happens 6 times in a row

I'm currently using Partition and have got it to count the number of Outcomes per UserID, but i am struggling to reset the Row Number when the Outcome changes for a 2nd time per UserID

 select  CallID,
        UserID,
        Outcome,
        DateOfCall
        rnk = ROW_NUMBER() OVER (PARTITION BY UserID , outcome ORDER BY DateOfCall ASC)
        from    Calls
        order by UserID, DateOfCall

Gives me the following for a UserID

- 19/01/2017 12:00 - Yes - 1 
- 19/01/2017 12:01 - Yes - 2
- 19/01/2017 12:02 - Yes - 3
- 19/01/2017 12:03 - No  - 1
- 19/01/2017 12:04 - No  - 2
- 19/01/2017 12:05 - No  - 3
- 19/01/2017 12:06 - Yes - 4
- 19/01/2017 12:07 - Yes - 5
- 19/01/2017 12:08 - No  - 4
- 19/01/2017 12:09 - No  - 5
- 19/01/2017 12:10 - No  - 6

As you can see the call at 12:10 will trigger as it is the 6th 'No' event, however only 3 happened in a row on both occasions.

Any help would be greatly appreciated

Answer 1

You can do what you want using the "difference of row numbers" approach:

select c.*,
       row_number() over (partition by userid, outcome, seqnum_u - seqnum_uo
                          order by dateofcall
                         ) as rnk
from (select c.*,
             row_number() over (partition by userid order by dateofcall) as seqnum_u,
             row_number() over (partition by userid, outcome order by dateofcall) as seqnum_uo
      from Calls c
     ) c
order by UserID, DateOfCall;

This logic is tricky the first time you see it (hey, maybe even the third or fifth time too). I advise you to run the inner query so you can see why the difference identifies groups of adjacent status values. Then the row_number() in the outer query make more sense.