Summarize data based on weeks

UDA
Enthusiast

Summarize data based on weeks

Hi,

I have a table with the following fields

Point_of_sale_Dt Item_ID Total_dollars Total_Units

01/06/2007 1000 50 50
01/02/2007 1000 100 100
01/11/2007 2000 50 50
01/12/2007 2000 50 50
01/25/2007 1000 75 75
01/22/2007 1000 75 75

Result:

Item_ID Total_Dollars Total_Units
1000 150 150
2000 100 100
1000 150 150

The result is a Group By item_id. and at the same time the Point_of_sale date should be grouped into weeks.

We have another table which has the weeks information:

YearKey Weekey WeekStartDt WeekendDt

2009 1 01/01/2009 01/11/2009
2009 2 01/12/2009 01/18/2009
2009 3 01/19/2009 01/25/2009
2009 4 01/26/2009 02/01/2009
2009 5 02/02/2009 02/08/2009
2009 6 02/09/2009 02/15/2009
2009 7 02/16/2009 02/22/2009
8
9 ... and so on

Need help is getting the result based on the weeks.
4 REPLIES
Enthusiast

Re: Summarize data based on weeks

figured it out myself..

select
tfc.point_of_sale_dt,sum(tfc.total_dollars),sum(tfc.total_units),tdw.yearkey,tdw.weekkey,tdw.weekstart_dt , tdw. weekend_dt
from
(
sel point_of_sale_dt,item_id, total_dollars,total_units
from tablea) TFC
left outer join
WeeksTable TDW
on
extract( year from tfc.point_of_sale_dt)=tdw.yearkey
and
tfc.point_of_sale_dt between tdw.weekstartdt and tdw. weekenddt
group by 1,4,5,6,7;
Enthusiast

Re: Summarize data based on weeks

actually its...

select
tfc.item_id,sum(tfc.total_dollars),sum(tfc.total_units),tdw.yearkey,tdw.weekkey,tdw.weekstart_dt , tdw. weekend_dt
from
(
sel point_of_sale_dt,item_id, total_dollars,total_units
from tablea) TFC
left outer join
WeeksTable TDW
on
extract( year from tfc.point_of_sale_dt)=tdw.yearkey
and
tfc.point_of_sale_dt between tdw.weekstartdt and tdw. weekenddt
group by 1,4,5,6,7;

Enthusiast

Re: Summarize data based on weeks

Try this as week days we can directly extract

Select Item_ID,sum(Total_dollars),sum(Total_Units)
From ttemp.ach96_sales
Group By week(Point_of_sale_Dt) , Item_ID
Order By week(Point_of_sale_Dt)
Senior Apprentice

Re: Summarize data based on weeks

WEEK is not a Teradata SQL function, it's ODBC SQL.
It will only work using ODBC in SQL Assistant if you switch on
Tools - Options - Allowe Use of ODBC SQL Extensions in Queries

And it's based on some strange algorithm which might return the correct week number in your part of the world.
The original query is based on weeks starting on monday, not sunday.

To Nick:
this query might get a badly skewed spool, at least there's a probably bad product join.
It's usually recommended to put that kind of information into your calendar table, i.e. a new column for week_number and then join point_of_sale_dt to it.

Dieter