At the end of every month I need to create a report calculating some KPIs for the last 24 months. For example, for april 2016, calculate KPI1, KPI2,KPI3 etc from '2016-04-30' to 2014-05-01'. Again for May 2016, calculate KPIs from '2016-05-31' to '2014-06-01' and so on.
Is there a way to do this in teradata recursively? Thanks.
Can you rollup the data into one row per month and then simply apply OLAP-functions like
SUM(col) OVER (ORDER BY year_month ROWS 23 PRECEDING)
In Standard SQL there's
RANGE, too, but Teradata only supports
These are the separate KPI calculating queries for a single month. How to implement it for every month.
Ouch, this might be possible, but would be quite hard to implement as a single query for 24 months.
But it's a one time only task to create the initial 24 months, after that you run the query only once a month.
You cOULD combine /*2*/ and /*3*/ into a single Insert/Select and use it within a Stored Procedure to loop over 24 months.