Grouping Data to Create a View
I have a table that has sales data for different locations, one record per location for each day:
location_num date amount1 amount2
1 12/7/98 100.00 10.00
1 12/8/98 150.00 5.00
.
.
1 12/13/98 100.00 10.00
2 12/7/98 50.00 5.00
.
.
2 12/13/98 120.00 12.00
I want to create a view that sums the amounts for each week for each location:
location_num week_ending amount1 amount2
1 12/13/98 700.00 80.00
2 12/13/98 650.00 25.00
I know how to create a select statement when I have the beginning and ending date for the week:
select location_num, sum(amount1), sum(amount2) from xyz where date between '12/7/98' and '12/13/98'.
However, I want to create a view that will sum up the sales for each week without hardcoding any dates into it; I just want to specify that a week is Monday - Sunday and have Sunday show as the week ending date.
I'm not sure this is possible in a view. I know that I can create a table, but I don't want to worry about it getting out of sync (which is actually the situation I am in right now). Does anyone have any ideas how to go about this?
location_num date amount1 amount2
1 12/7/98 100.00 10.00
1 12/8/98 150.00 5.00
.
.
1 12/13/98 100.00 10.00
2 12/7/98 50.00 5.00
.
.
2 12/13/98 120.00 12.00
I want to create a view that sums the amounts for each week for each location:
location_num week_ending amount1 amount2
1 12/13/98 700.00 80.00
2 12/13/98 650.00 25.00
I know how to create a select statement when I have the beginning and ending date for the week:
select location_num, sum(amount1), sum(amount2) from xyz where date between '12/7/98' and '12/13/98'.
However, I want to create a view that will sum up the sales for each week without hardcoding any dates into it; I just want to specify that a week is Monday - Sunday and have Sunday show as the week ending date.
I'm not sure this is possible in a view. I know that I can create a table, but I don't want to worry about it getting out of sync (which is actually the situation I am in right now). Does anyone have any ideas how to go about this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scratch the last datediff in the "group by"... it isn't needed, and using it creates a problem when a week crosses a change in years (i.e., 1998 -1999).
ASKER
Thank you for your help tschill, just one question:
What is the significance of '1/7/1900'? In general I understand how the datediff function works, but I don't understand why using '1/7/1900' works. What would I use if I want my week ending to be on a different day? Sorry, to be such a newbie!
What is the significance of '1/7/1900'? In general I understand how the datediff function works, but I don't understand why using '1/7/1900' works. What would I use if I want my week ending to be on a different day? Sorry, to be such a newbie!
No problem... There is nothing significant about 1/7/1900. I choose it starting point for weeks, figuring it would be before any of the dates in your table, and not knowing if it being a Sunday made a difference... don't think it does (any date in that same week would been OK). I'd have to test a bit more, though.
If you want to use a different day for a week ending you'd probably have to do a dateadd() to adjust the dates, since it appears that "wk" in datediff goes from Sunday-Saturday.
If you want to use a different day for a week ending you'd probably have to do a dateadd() to adjust the dates, since it appears that "wk" in datediff goes from Sunday-Saturday.
ASKER
I'm not sure where to use the dateadd. If I add days to '1/7/1900' it gives me the same result (Sun. to Sat.).
I'm really making you work for the points, aren't I? :)
I'm really making you work for the points, aren't I? :)
I haven't tested the effect on the rest of the query, but the idea is that adding (-1) days to the second date in the datediff function will move the start day from Sunday to Monday... the following code uses the same four dates, but in the first set the week number changes on Sunday, in the second set it changes on Monday.
set nocount on
select 'Sunday - Saturday'
select datediff(wk, '1/7/1900', '12/18/1998') as 'Friday',
datediff(wk, '1/7/1900', '12/19/1998') as 'Saturday',
datediff(wk, '1/7/1900', '12/20/1998') as 'Sunday',
datediff(wk, '1/7/1900', '12/21/1998') as 'Monday'
select 'Monday - Sunday'
select datediff(wk, '1/7/1900', dateadd(dd, -1, '12/18/1998')) as 'Friday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/19/1998')) as 'Saturday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/20/1998')) as 'Sunday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/21/1998')) as 'Monday'
set nocount on
select 'Sunday - Saturday'
select datediff(wk, '1/7/1900', '12/18/1998') as 'Friday',
datediff(wk, '1/7/1900', '12/19/1998') as 'Saturday',
datediff(wk, '1/7/1900', '12/20/1998') as 'Sunday',
datediff(wk, '1/7/1900', '12/21/1998') as 'Monday'
select 'Monday - Sunday'
select datediff(wk, '1/7/1900', dateadd(dd, -1, '12/18/1998')) as 'Friday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/19/1998')) as 'Saturday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/20/1998')) as 'Sunday',
datediff(wk, '1/7/1900', dateadd(dd, -1, '12/21/1998')) as 'Monday'
ASKER
doh! I didn't think about changing the second date.
Thanks for all of your help.
Thanks for all of your help.