How to Calculate Moving Sum with Reset Based on Condition in Teradata SQL

Teradata SQL: Calculate running totals if a condition is met

If it's only two weeks/rows your query can be further simplified to a single STATS-step in Explain (as both OLAP-functions apply the same PARTITION/ORDER) :

SELECT T.*
, CASE 
    WHEN MAX(WEEK_NUMBER) OVER (PARTITION BY CUSTOMER ORDER BY WEEK_NUMBER ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + 1 = WEEK_NUMBER
    THEN SUM(AMOUNT)      OVER (PARTITION BY CUSTOMER ORDER BY WEEK_NUMBER ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
   ELSE AMOUNT
  END AS TWO_WEEK_SUM_AMOUNT
FROM MY_TABLE T
ORDER BY CUSTOMER, WEEK_NUMBER

Of course this assumes that weeks start with 0 and there's no previous year week 52/53.

Teradata Window/Rolling Sum under Multiple Conditions

The only way I ever found to return a result like this utilizes recursion.

You will get the fastest speed if you materialize the data as a Multiset Volatile Table with the partition columns (REPORT_DT, SEG_CD, NUM_F) as Primary Index first and then start the recursion:

WITH RECURSIVE cte AS
(   
SELECT
  RN,
  REPORT_DT,
  SEG_CD,
  NUM_F,
  T1,
  T2,
  ELAPSED_TIME,
  CASE WHEN ELAPSED_TIME >= 20 
       THEN 0 
       ELSE ELAPSED_TIME
  END AS sum_ELAPSED_TIME,
  CASE WHEN sum_ELAPSED_TIME = 0 
       THEN 1 
       ELSE 0
  END AS FLAG
FROM vt
WHERE rn = 1

UNION ALL

SELECT
  vt.RN,
  vt.REPORT_DT,
  vt.SEG_CD,
  vt.NUM_F,
  vt.T1,
  vt.T2,
  vt.ELAPSED_TIME,
  CASE WHEN cte.sum_ELAPSED_TIME + vt.ELAPSED_TIME >= 15 
       THEN 0 
       ELSE cte.sum_ELAPSED_TIME + vt.ELAPSED_TIME 
  END AS new_ELAPSED_TIME,
  CASE WHEN new_ELAPSED_TIME = 0
       THEN 1 
       ELSE 0
  END AS FLAG
FROM vt 
JOIN cte 
  ON vt.REPORT_DT = cte.REPORT_DT
 AND vt.SEG_CD = cte.SEG_CD
 AND vt.NUM_F = cte.NUM_F
 AND vt.rn = cte.rn + 1
) 
SELECT * FROM cte

reset a running total in same partition based on a condition

Do a running total on ResetSum in a derived table and use that as a partition column in the running total on Amount.

select T.PersonID,
       T.Amount,
       T.PayDate,
       sum(T.Amount) over(partition by T.PersonID, T.ResetSum 
                          order by T.PayDate rows unbounded preceding) as SumAmount
from (
     select T1.PersonID,
            T1.Amount,
            T1.PayDate,
            sum(case T1.ResetSum 
                  when 1 then 1 
                  else 0 
                end) over(partition by T1.PersonID 
                          order by T1.PayDate rows unbounded preceding) as ResetSum
     from dbo.Table_1 as T1
     ) as T;

SQL Fiddle

How to sum data from multiple rows, based on a condition in Teradata?

You can do a window sum, but you need to remove the rows clause: since you specified rows unbounded preceding, the query only considers the preceding rows, while you want to sum over the entire partition:

select 
    t.*, 
    sum(money) over(partition by country, system) systemsum
from mytable as t

Side note: specifying preceding rows without an order by does not really make sense - with this syntax, you get an undefined set of rows being considered within the partition.

Teradata SQL: Calculate number of successive occurrences of error code (and reset on non occurrence)

You can subtract row_number() from the date and aggregate:

select item, err_cd, count(*)
from (select t.*,
             row_number() over (partition by item, err_cd order by run) as seqnum
      from t
     ) t
group by item, err_cd, run - seqnum
order by item, err_cd, min(run);

---

Related Topics