Rolling Sum

Database
New Member

Rolling Sum

Fairly simple table - 3 columns:

  1. Location ID
  2. Date (YYYY-DD-MM)
  3. Sales (for that date)

For every location, for each date in 2017, I want to find the prior 6 months of sales (up to, but not including, the date). Final output would be:

  1. Location ID
  2. Date
  3. Last 6 months of sales

Fairly new to Teradata, so no idea on how to accomplish. Any help is appreciated!

2 REPLIES
Senior Apprentice

Re: Rolling Sum

Hi,

 

You need to use 'window aggregate functions' (look here - you may then need to search for 'ordered analytical functions').

 

Try the following:

SELECT sales_date
  ,location_id
   ,SUM(daily_sales_tot) OVER(PARTITION BY location_id ORDER BY sales_date ROWS BETWEEN 180 PRECEDING AND 1 PRECEDING) AS cumulative_sales
FROM table-name
ORDER BY 2,1;

Note that this code (and what the dbms does) is to scan "180 rows" - which is not necessarily "180 dates". To ensure this is accurate you need to have data for each date.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: Rolling Sum

Couple of extra thoughts.

 

Assume that you only want to see a result for dates in 2017, then

1) add a WHERE clause selecting 'sales_date' from 1st July 2016 to 31 Dec 2017

2) add a QUALIFY clause selecting 'sales_date' from 1st January to 31 Dec 2017

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com