I am trying to create a cumulative value with something like this
KEY1 Date_ VAL1 CUMU_VAL2
K1 D1 1 0
K1 D2 1 1
K1 D3 0 2
K1 D4 1 0
K1 D5 1 1
So, the issue is basically to keep on adding the value by 1 in column CUMU_VAL2 based on the previous row in VAL1, but this sum resets when the previous value in VAL1 column is zero. Basically if you do it in excel the formula for say Cell(D3) is
D3 = IF(C2>0, D2+1, 0)
I believe I should be able to something like this, but how do I add in the Case when previous value is zero then reset the sum?
SELECT
a1.*,
SUM(a1.VAL1) OVER (PARTITION BY a1.KEY1 ORDER BY a1.Date_ ) AS CUMU_VAL2
FROM source_table a1
My amendment to @GordonLinoff's answer as the OP didn't quite understand what I meant.