How to calculate moving sum with reset based on condition in teradata SQL?

Question

I have this data and I want to sum the field USAGE_FLAG but reset when it drops to 0 or moves to a new ID keeping the dataset ordered by SU_ID and WEEK:

SU_ID   WEEK    USAGE_FLAG
100        1    0
100        2    7
100        3    7
100        4    0
101        1    0
101        2    7
101        3    0
101        4    7
102        1    7
102        2    7
102        3    7
102        4    0

So I want to create this table:

SU_ID   WEEK    USAGE_FLAG    SUM
100        1    0             0
100        2    7             7
100        3    7             14
100        4    0             0
101        1    0             0
101        2    7             7
101        3    0             0
101        4    7             7
102        1    7             7
102        2    7             14
102        3    7             21
102        4    0             0

I have tried the MSUM() function using GROUP BY but it won't keep the order I want above. It groups the 7's and the week numbers together which I don't want.

Anyone know if this is possible to do? I'm using teradata

Cheers bluefeet, how did you edit it like a table? – wilsonm2 Dec 11 '12 at 11:50 — wilsonm2, Dec 11 '12 at 11:50

score 6 · Answer 1 · 2012-12-11T12:07:22.223

In standard SQL a running sum can be done using a windowing function:

select su_id,
       week,
       usage_flag, 
       sum(usage_flag) over (partition by su_id order by week) as running_sum
from the_table;

I know Teradata supports windowing functions, I just don't know whether it also supports an order by in the window definition.

Resetting the sum is a bit more complicated. You first need to create "group IDs" that change each time the usage_flag goes to 0. The following works in PostgreSQL, I don't know if this works in Teradata as well:

select su_id,
       week,
       usage_flag,
       sum(usage_flag) over (partition by su_id, group_nr order by week) as running_sum
from (
  select t1.*,
         sum(group_flag) over (partition by su_id order by week) as group_nr
  from (
      select *,
             case
                when usage_flag = 0 then 1
                else 0
              end as group_flag
      from the_table
  ) t1
) t2
order by su_id, week;

Thanks, does this work as in creating my desired table? I've tried it in teradata but it shows the total sum per group of su_id. — wilsonm2, Dec 11 '12 at 12:20
@wilsonm2: I don't know what's different in Teradata, but the above returns exactly what you want in PostgreSQL: http://sqlfiddle.com/#!12/2046f/1 and Oracle: http://sqlfiddle.com/#!4/ee6a1/2 — , Dec 11 '12 at 12:22
So it does, thanks! I'm going to try and play around with it in teradata and see what the difference is. — wilsonm2, Dec 11 '12 at 15:20

score 3 · Answer 2 · edited Jun 19 '14 at 11:37

Try below code, with use of RESET function it is working fine.

select su_id,
       week,
       usage_flag, 
       SUM(usage_flag) OVER (
        PARTITION BY su_id
        ORDER BY week
        RESET WHEN usage_flag < /* preceding row */ SUM(usage_flag) OVER (
             PARTITION BY su_id ORDER BY week
             ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
        ROWS UNBOUNDED PRECEDING
   )
from emp_su;

score 0 · Answer 3 · edited Jul 06 '17 at 08:59

0

Please try below SQL:

select su_id,
       week,
       usage_flag, 
       SUM(usage_flag) OVER (PARTITION BY su_id ORDER BY week
        RESET WHEN usage_flag = 0 
        ROWS UNBOUNDED PRECEDING
   )
from emp_su;

Here RESET WHEN usage_flag = 0 will reset sum whenever sum usage_flag drops to 0

edited Jul 06 '17 at 08:59

scandel

1,692
3
20
39

answered Jul 06 '17 at 08:19

Rohit Khattri

11
1

How to calculate moving sum with reset based on condition in teradata SQL?

3 Answers3

Linked