August 20, 2009 at 10:02 am
Hi, I would like to group an aggregate sum / count by week of year but in the report show the date range of that week ie, for this week #34, the date range would be July 16th - July 22nd.
Below is the mock query that I've been working with.
Thanks in advance...
select
datepart(wk, datefield1) as 'WeekOfYear',
--put daterange column here,
sum(col1) as 'Col1Base',
sum(col2) as 'Col2Base'
from
tbl_Base
where
datepart(mm, datefield1) between 1 and 8
group by datepart(wk, duedate)
with rollup
August 20, 2009 at 10:17 am
select
datepart(wk, datefield1) as 'WeekOfYear',
--put daterange column here,
sum(col1) as 'Col1Base',
sum(col2) as 'Col2Base'
from
tbl_Base
where
datepart(mm, datefield1) between 1 and 8
group by datepart(wk, duedate)
with rollup
Ok, I think this works, seems a little hacky, if anyone has a better way, I'd still like any suggestions...
select
datepart(wk, datefield1) as 'WeekOfYear',
convert(varchar, dateadd( dd, 1 - datepart( dw, duedate),duedate ), 107) +
' - ' +
convert(varchar, dateadd( dd, 7 - datepart( dw, duedate), duedate ), 107) as 'DateRange',
sum(col1) as 'Col1Base',
sum(col2) as 'Col2Base'
from
tbl_Base
where
datepart(mm, datefield1) between 1 and 8
group by datepart(wk, duedate)
with rollup
August 20, 2009 at 11:19 am
Does this help ?
--
select getdate() as today
,DATEPART(wk,getdate()) as WeekOfYear
,left(DATEADD(wk,DATEDIFF(wk,0,getdate()),-1),6)+ ' - '
+left(DATEADD(wk,DATEDIFF(wk,0,getdate()),5),6) as weekRange
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 11:41 am
--
select getdate() as today
,DATEPART(wk,getdate()) as WeekOfYear
,left(DATEADD(wk,DATEDIFF(wk,0,getdate()),-1),6)+ ' - '
+left(DATEADD(wk,DATEDIFF(wk,0,getdate()),5),6) as weekRange
--
Hi Bob, that does work, but does that just work for this week and today?
The nested datediff function, gives me the week from the first system week ie, 5720...and then how does the dateadd function work? if the value of the datediff is 5720 how does the dateadd function manipulate that function to get the beginning and end of that week? Just trying to really understand without just copying and pasting into my query.
August 20, 2009 at 12:16 pm
Fair questions. Play around with this for a minute and you will see that it works for other dates than today. Explanations follow.
-- edited to add missing column
declare @date datetime
set @date = '11/11/2008'
select @date as specifiedDate
,LEFT(@date,20) as [Date As String]
,DATEDIFF(WK,0,@date) as [Alltime Number of Weeks]
,DATEPART(wk,@date) as WeekOfYear
,left(DATEADD(wk,DATEDIFF(wk,0,@date),-1),6)+ ' - '
+left(DATEADD(wk,DATEDIFF(wk,0,@date),5),6) as weekRange
--
First, you need to understand what the DATEDIFF function is doing. Because I passed it 'WK' as the first parameter, it calculates the number of weeks from Day 0 to Day X (the specified date). The result is simply an integer. In the query above I'm calling that the all time number of weeks.
Using different parameters, DATEDIFF returns the number of seconds, minutes, hours, days, months or years as well.
The DATEADD function wrapped around the DATEDIFF then adds that number of weeks to one day before Day 0. I did that to get a Sunday start date on my system. Using DATEADD(DATEDIFF()) is a fairly common trick to quickly get the first day or last day of a week or month. Adding that number of weeks to 5 days after Day 0 gave me the Saturday ending date.
Like DATEDIFF, DATEADD can also take parameters representing other time units: days, months, years, hours, minutes, seconds.
Using the LEFT function just happens to convert a datetime into a character string. See the dateAsString column above. Each month is abbreviated with three letters and the day can be two at most, so six characters covers that plus any spaces. String the two LEFT character strings together with a dash in the middle and there you have it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 1:48 pm
Bob Hovious (8/20/2009)
Fair questions. Play around with this for a minute and you will see that it works for other dates than today. Explanations follow.
-- edited to add missing column
declare @date datetime
set @date = '11/11/2008'
select @date as specifiedDate
,LEFT(@date,20) as [Date As String]
,DATEDIFF(WK,0,@date) as [Alltime Number of Weeks]
,DATEPART(wk,@date) as WeekOfYear
,left(DATEADD(wk,DATEDIFF(wk,0,@date),-1),6)+ ' - '
+left(DATEADD(wk,DATEDIFF(wk,0,@date),5),6) as weekRange
--
First, you need to understand what the DATEDIFF function is doing. Because I passed it 'WK' as the first parameter, it calculates the number of weeks from Day 0 to Day X (the specified date). The result is simply an integer. In the query above I'm calling that the all time number of weeks.
Using different parameters, DATEDIFF returns the number of seconds, minutes, hours, days, months or years as well.
The DATEADD function wrapped around the DATEDIFF then adds that number of weeks to one day before Day 0. I did that to get a Sunday start date on my system. Using DATEADD(DATEDIFF()) is a fairly common trick to quickly get the first day or last day of a week or month. Adding that number of weeks to 5 days after Day 0 gave me the Saturday ending date.
Like DATEDIFF, DATEADD can also take parameters representing other time units: days, months, years, hours, minutes, seconds.
Using the LEFT function just happens to convert a datetime into a character string. See the dateAsString column above. Each month is abbreviated with three letters and the day can be two at most, so six characters covers that plus any spaces. String the two LEFT character strings together with a dash in the middle and there you have it.
Thanks for taking the time to give a detailed explanation Bob, after playing around it makes more sense to me.
August 20, 2009 at 2:42 pm
You're welcome, Marcus, and much success to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply