Cummulative sum per month and removing inactive values from previous month

General
Highlighted
New Member

Cummulative sum per month and removing inactive values from previous month

Hi All,

 

I am relatively new to Teradata SQL.

 

I am doing some analysis on history of Product activated on large sets of data.

 

The raw data have values like

MonthProduct activated
Jan-1810,000
Feb-1812,000
Mar-188,000
Apr-1810,000
May-186,000

 

I am trying to add another column for the running total which gives me result like

 

MonthService activatedRunning sum
Jan-1810,00010,000
Feb-1812,00022,000
Mar-188,00030,000
Apr-1810,00040,000
May-186,00046,000

 

I am using hte Teradata SQL script

 

Select 

extract(year from service.Activation_Date) || extract(month from service.Activation_Date) as Activation_month,


service.Product_name,
SUM(count(service.Product_name)) OVER (PARTITION BY service.Product_name ORDER BY Activation_month ROWS UNBOUNDED PRECEDING) AS CUMMULATIVE

from

(
Select
a.Activation_Date 
a.name as Product_name,
a.stauts as Product_status -- this could be active or inactive
a.name as Contract_Name
From product a
where 
a.name like ('%XYZ%')
) Contract

Group by 1,2

However the problem I am facing here is that historically there are some cusotmer who cancel the product so need to substract it from runnig total as well.

 

 

I am looking to get some assitance on how I can substract inactive values from running total?

1 REPLY
Junior Contributor

Re: Cummulative sum per month and removing inactive values from previous month

You can apply conditional aggregation like:

SUM(SUM(CASE WHEN Product_status = 'active' then 1 else -1 end)) 
OVER (PARTITION BY service.Product_name ORDER BY Activation_month ROWS UNBOUNDED PRECEDING) AS CUMMULATIVE