Fairly simple table - 3 columns:
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:
Fairly new to Teradata, so no idea on how to accomplish. Any help is appreciated!
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.
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