20 May 2016
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.
20 May 2016
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.
This thread might help:
http://forums.teradata.com/forum/database/recursive-row-function#comment-147359
Dieter
You must sign in to leave a comment.
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.