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
I am trying to add another column for the running total which gives me result like
|Month||Service activated||Running sum|
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?
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