General
Enthusiast

Running Sum counter restart when threshold value reached

Hi All,

I have a unique situation in that I have the following scenario:

Cust      UID     Txn Amount      Running Sum Amount            indicator

1            a             10                        10                                      0

1            a             15                        25                                      1

1            a             12                        37                                      0

1            a             10                        47                                      0

1            a             50                        97                                      1

What I want is to set the indicator to 1 every time the running sum or total has crossed a value of 25. In effect, when the running sum crosses the threshold value of 25, I start the running sum again from 0 and start to add the Txn Amount values till it corsses the 25 threshold value again at which time I start the cycle again. Is there any direct way of doing this?

I have tried using sum over recursive but am lost when trying to reset the counter after the first threshold value cross. Any help yould be most appreciated.

Also, is there any way to reference a computed column value during recursive run (previous rows computed value/column)?

Best Regards,

Arun Krishnan.

Tags (2)
2 REPLIES
Junior Supporter

Re: Running Sum counter restart when threshold value reached

Hi.

I think your logic is flawed, because you'll need an ordering column to guarantee consistent results.

Anyhow, a logic like this may give you what you're after:

`SELECT CUST,       UID,       TXN_AMOUNT,       ACCUM,       CASE WHEN MIN(ACCUM) OVER (PARTITION BY CUST,(ACCUM/25)                                  ORDER BY CUST,(ACCUM MOD 25) ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL AND ACCUM >= 25            THEN 1 ELSE 0 END FLG25  FROM ( SELECT CUST,                UID,                TXN_AMOUNT,                SUM(TXN_AMOUNT) OVER (PARTITION BY CUST ORDER BY CUST ROWS UNBOUNDED PRECEDING) ACCUM            FROM PRUEBA_21) pre; *** Query completed. 5 rows found. 5 columns returned. *** Total elapsed time was 1 second.       CUST  UID   TXN_AMOUNT        ACCUM  FLG25-----------  ---  -----------  -----------  -----          1  a             12           12      0          1  a             50           62      1          1  a             10           72      0          1  a             15           87      1          1  a             10           97      0`

HTH.

Cheers.

Carlos.

Junior Contributor

Re: Running Sum counter restart when threshold value reached

You have to use recursion for this task, OLAP functions don't work here.

First you need a way to advance to the next row, so create a Volatile Table with a ROW_NUMBER() with the same PARTITON/ORDER you used for the running sum.

And then it's adding the new amount to the previous sum and a CASE to check if it's > 25.