How to reset running counts and start from 1 based on the condition and make previous row values as-CodePudding

For example there is some table with below data:

No    Id    Value
1    100    1
2    100    0
3    100    1
4    100    2

1    101    1
2    101    2

1    102    0
2    102    1

I have to write SQL query, which will return row count based on specific condition. If the value matches 0 then need to reset running counts and start from 1 and make previous row values as 0

So the result will be like:

No    Id    Value    Running Count
1    100    1       0
2    100    0       0
3    100    1       1
4    100    1       2

1    101    1       1
2    101    2       2

1    102    1       0
2    102    0       0

CodePudding user response：

Your sample dataset is quite limited so I'm not sure of all edge cases but see if the following works for you. If not it might help get you there.

This gets a running count using a window & case expression and uses lead to check the next value.

If the current value or next value is 0 the count is 0, otherwise it's the running count subtracting 1 if there is a 0 in the Id block indicating the count was reset.

select No, Id, Value, 
  case when value = 0 or nv = 0 
  then 0 
  else 
    rc - case when Min(value) over(partition by id) = 0 then 1 else 0 end
  end Running_Count
from (
  select *, 
    Sum(case when value = 0 then 0 else 1 end) over(partition by id order by no) rc,
    Lead(Value) over(partition by Id order by No)nv
  from t
)t;