'How to cumulative sum in Teradata using multiple conditions?

I have been struggling to figure out how to turn this process into SQL. See the image for the structure for the data and the manual calculations being done.

I have data where I need to do a running sum but the way the sum works changes based on another column.

I have a day and a week column

  1. The first value of the group is the first day value
  2. When the week value exists, the value is the sum of all of the existing weeks in the group
  3. For the rest of the days, the value is the running sum of the group

The 'desired result' column is want, and the 'calcs' columns is the manual calculation to get the row value

See image here

Tab Delimited Data

Country_NM  Activity_NM State_NM    Year_Act    Date_Act    Day_ACT Week_ACT    Desired Result  Calcs
United States   Running Georgia PPY 3/1/2020    446     446 ( = 446)
United States   Running Georgia PPY 3/2/2020    554     1000    ( = 446 + 554)
United States   Running Georgia PPY 3/3/2020    535 2,435   2,435   ( = 2435)
United States   Running Georgia PPY 3/4/2020    471     2,906   ( = 2435 + 471)
United States   Running Georgia PPY 3/5/2020    351     3,257   ( = 2906 + 351 )
United States   Running Georgia PY  3/1/2021    444     3,701   ( = 3257 + 444)
United States   Running Georgia PY  3/2/2021    514     4,215   ( = 3701 + 514 )
United States   Running Georgia PY  3/3/2021    467 2,762   5,197   ( = 2435 + 2762)
United States   Running Georgia PY  3/4/2021    480     5,677   ( = 5197 + 480)
United States   Running Georgia PY  3/5/2021    365     6,042   ( = 5677 + 365)
United States   Running Georgia CY  3/1/2022    350     6,392   ( = 6042 + 350)
United States   Running Georgia CY  3/2/2022    387     6,779   ( = 6392 + 387)
United States   Running Georgia CY  3/3/2022    474 3,344   8,541   ( = 2435 + 2762 + 3344)
United States   Running Georgia CY  3/4/2022    221     8,762   ( = 8541 + 221)
United States   Running Georgia CY  3/5/2022    312     9,074   ( = 8762 + 312)
United States   Swimming    Georgia PPY 3/1/2020    457     457 ( = 457)
United States   Swimming    Georgia PPY 3/2/2020    592     1,049   
United States   Swimming    Georgia PPY 3/3/2020    618 3,344   3,344   
United States   Swimming    Georgia PPY 3/4/2020    548     3,892   
United States   Swimming    Georgia PPY 3/5/2020    443     4,335   
United States   Swimming    Georgia PY  3/1/2021    453     4,788   
United States   Swimming    Georgia PY  3/2/2021    540     5,328   
United States   Swimming    Georgia PY  3/3/2021    517 2,435   5,779   
United States   Swimming    Georgia PY  3/4/2021    561     6,340   
United States   Swimming    Georgia PY  3/5/2021    412     6,752   
United States   Swimming    Georgia CY  3/1/2022    436     7,188   
United States   Swimming    Georgia CY  3/2/2022    444     7,632   
United States   Swimming    Georgia CY  3/3/2022    500 2,436   8,215   
United States   Swimming    Georgia CY  3/4/2022    290     8,505   
United States   Swimming    Georgia CY  3/5/2022    392     8,897
sqlsumteradatacumulative-sum


Solution 1:[1]

Consider using window functions for a running sum first of Week_Act, then another running sum on calculated Day_Act and adding the running week sum as an addition partition grouping:

WITH week_cte AS (
    SELECT Country_Nm, Activity_Nm, State_Nm, 
           Year_Act, Date_Act, Day_ACT, Week_ACT,
           SUM(ZEROIFNULL(Week_ACT)) 
             OVER(PARTITION BY Activity_Nm
                  ORDER BY Date_Act ROWS UNBOUNDED PRECEDING) AS Week_Cum_Sum
    FROM myTable
)

SELECT *,
       SUM(CASE 
              WHEN Week_ACT IS NOT NULL
              THEN Week_Cum_Sum
              ELSE Day_ACT
           END
          ) OVER(PARTITION BY Activity_Nm, Week_Cum_Sum
                 ORDER BY Date_Act ROWS UNBOUNDED PRECEDING) AS Day_Cum_Sum
FROM week_cte

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Parfait

Related Questions