Create a column with Conditional Recursive SQL / based on a condition

user_15055055 0
Viewed 68

Consider the table ordered by id with column IsSatisfied.

ID IsSatisfied
1 0
2 1
3 0
4 0
5 1
6 0
.... ...

I would like to create another column State, that initially takes value 0 and changes it between 0 and 1, only when the value of Isatisfied is equal to 1, as in the table below.

ID IsSatisfied State
1 0 0
2 1 1
3 0 1
4 0 1
5 1 0
6 0 0
.... ... ...

I tried LAG() function or recursive CTE, but unfortunately failed. The closest solution that I have found is Conditional Recursive SQL Select, but I was not able to convert it to suit my needs.

tsql sql-server sql
1 Answers

If I understand correctly, you want a cumulative sum of issatisfied -- and then the remainder when divided by 2:

select t.*,
       ( sum(issatisfied) over (order by id) % 2 ) as state
from t;
user_11440350
Related