CODEWITHSUNDEEP
sqlsumteradata
user2888246
user2888246

Reputation: 185

SQL Sum by week from daily table

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

Upvotes: 3

Views: 7489

Answers (2)

Rob Paller
Rob Paller

Reputation: 7786

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;

Upvotes: 3

Hart CO
Hart CO

Reputation: 34774

I know very little about TERADATA, but I believe you can leverage the sys_calendar.calendar table, something like:

SELECT s.Product, c.week_of_year, SUM(s.sales) AS Sales
FROM sales AS s
JOIN sys_calendar.calendar as C
  ON s.date = c.date

You'd need the Year in there as well, so as to not group up week 1 of 2013 with week 1 of 2012.

Upvotes: 1

Related Questions