Is there a way to look at the previous evaluation within a CASE expression?

I’ve created the following table in Excel as an example. I would like to do a similar expression (like the formula shown in Excel) in SQL Server on a similar looking table. The columns row_num and switch were already created based on previous CASE statements.

Click for Excel sheet

Is there any way to achieve this?

Thanks for having a look!

PS: in case image does not work:

EXCEL     |   A    |    B   |   C   | 
1         |row_num | switch | group |
2         | 1      | 0      | 1     |
3         | 2      | 1      | 1     |
4         | 3      | 1      | 1     |
5         | 4      | 0      | 2     |
6         | 5      | 0      | 3     |
7         | 6      | 1      | 3     |
8         | 7      | 1      | 3     |
9         | 8      | 0      | 4     |
10        | 1      | 0      | 5     |
11        | 2      | 1      | 5     |
12        | 3      | 0      | 6     |
13        | 4      | 1      | 6     |

Formula on cell C3: IF(B3=1;C2;C2+1) (etc)

Answer

You want a cumulative sum of the cases when switch = 0.

However, SQL tables represent unordered sets. So you need a column (or multiple columns) that specify the ordering. In your example, you can use the excel row number, but your data might have a more appropriate column.

Hence:

select t.*,
       sum(case when switch = 0 then 1 else 0 end) over (order by excel_row) as grouping
from t;

If switch only takes on two values, you can simplify this to:

select t.*,
       sum(1 - switch) over (order by excel_row) as grouping
from t;