RESET WHEN on a cumulative sum

Database
Highlighted
Teradata Employee

RESET WHEN on a cumulative sum

Hi all,

I'm trying to create a cumulative sum that resets when it hits  a certain value. The below query works up to the first reset but then every row after that also resets.  It seems that the underlying cumulative sum isn't actually resetting.  I'm sure I've make a simple error here, for as simple as anything is around windowed OLAP functions!

 

select RowNum,sum(RowNum) over (order by RowNum

                                                            reset when sum(RowNum) over (order by RowNum rows between unbounded preceding and current row) >= 100

                                                            rows between unbounded preceding and current row) RowSum

from (select row_number() over (order by Calendar_Date) RowNum from sys_calendar.calendar qualify RowNum <= 100) x;

 

The results I get are:
1     1

2     2

3     6

4     10

5     15

6     21

7     28

8     36

9     45

10     55

11     66

12     78

13     91

14     14

15     15

16     16

17     17

18     18

19     19

20     20

...

 

But what I'm after is:

1     1

2     2

3     6

4     10

5     15

6     21

7     28

8     36

9     45

10     55

11     66

12     78

13     91

14     14     Reset as CSUM would be >= 100

15     29

16     45

17     62

18     80

19     99

20     20     Reset as CSUM would be >= 100

...

 

Any help gratefully received.

 

Regards, Jim Hudd.

1 REPLY
Junior Contributor

Re: RESET WHEN on a cumulative sum

RESET WHEN doesn't work like that, you need a recursive approach, e.g.

https://community.teradata.com/t5/Database/Help-required-on-the-query-using-analytic-function/td-p/4...