SQL Sum by week from daily table

sql, sum, teradata

Solution

If you are using Teradata 14 you can leverage the `DayNumber_Of_Week()` function in the database `TD_SYSFNLIB`:

SELECT s.Product
     , s.Date + (7-DayNumber_Of_Week(s.date)) AS WeekEndingDate /* Saturday */
     , SUM(s.Sales) AS Sales
  FROM sales AS S
GROUP BY 1,2;

This should work in Teradata 13.10 as well.

Using Sys_Calendar:

SELECT s.Product
     , s.DATE + (7-c.Day_Of_Week) AS WeekEndingDate /* Saturday */
     , SUM(s.Sales) AS Sales
  FROM sales AS S
 INNER JOIN
       Sys_Calendar.Calendar c
    ON S.date = c.calendar_date
GROUP BY 1,2;

Problem

I have a table with sales for products. The sales are per day. like ``` product date sales 1 '2013-11-01' 100 1 '2013-11-02' 423 1 '2013-11-03' 700 1 '2013-11-04' 233 2 '2013-11-01' 623 2 '2013-11-02' 451 2 '2013-11-03' 9000 ``` I want to get a query which will show me the week over week sum of sales So something like: ``` product week ending sales 1 '2013-11-01' 10000 1 '2013-11-08' 15000 2 '2013-11-01' 4900 2 '2013-11-08' 30000 ``` I'm not sure how I get this weekly groups when summing up. I'm using teradata

Original source