Compare Current Row with Previous/Next row in SQL Server

Question

I have a table named team and it like below: I just added a row_number in the 3rd column

RaidNo  OutComeID   RN
2           15      1
4           15      2
6           14      3
8           16      4
10          16      5
12          14      6
14          16      7
16          15      8
18          15      9
20          16      10
22          12      11
24          16      12
26          16      13
28          16      14
30          15      15
32          14      16
34          13      17

When the OutcomeId came as 16 then start with one and 16 comes consecutively, add one by one. And the results be like

RaidNo  OutComeID   RN  Result
2           15      1   0
4           15      2   0
6           14      3   0
8           16      4   1
10          16      5   2
12          14      6   0
14          16      7   1
16          15      8   0
18          15      9   0
20          16      10  1
22          12      11  0
24          16      12  1
26          16      13  2
28          16      14  3
30          15      15  0
32          14      16  0
34          13      17  0

Help me to get the result.

Answer 1

You can use the following query:

SELECT RaidNo, OutComeID, RN,
       CASE 
          WHEN OutComeID <> 16 THEN 0
          ELSE ROW_NUMBER() OVER (PARTITION BY OutComeID, grp ORDER BY RN) 
       END AS Result
FROM (
   SELECT RaidNo, OutComeID, RN,
          RN - ROW_NUMBER() OVER (PARTITION BY OutComeID ORDER BY RN) AS grp
   FROM mytable) AS t
ORDER BY RN

Field grp identifies slices (also called islands) of consecutive records having the same OutComeID value. The outer query uses grp in order to enumerate each record that belongs to a '16' slice. The records that belong to the other slices are assigned value 0.

Demo here