Group By WEEK

Question

Post reply

Group By WEEK

2Tall

SSChampion

Points: 14644
More actions
August 2, 2010 at 8:10 am

#221985

Hello. It is not my intention to break the cardinal rule of posting regards supplying actual data/code but I am hoping my question is one you can answer without.
I have a query that returns order due dates. I would like to group them by week for reporting purposes.
Example: (+ used to mimic expanding groups)
+ 02 August 2010 Monday
.......02 August 2010 Monday
.......03 August 2010 Tuesday
.......etc....
.......08 August 2010 Sunday
+ 09 August 2010 Monday
.......10 August 2010 Monday
.......11 August 2010 Tuesday
.......etc....
.......15 August 2010 Sunday
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

dateadd(wk, datediff(wk, 0, @ThisDate), 0)

This will convert a date into the first Monday of the week that contains it. Group on this date.

Check out Lynn Pettis' wonderful one-stop-shop for common date functions: http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/3/25/some-common-date-routines.aspx.

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks for the swift response + link.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

jvanderberg (8/2/2010)
dateadd(wk, datediff(wk, 0, @ThisDate), 0)
This will convert a date into the first Monday of the week that contains it. Group on this date.
Check out Lynn Pettis' wonderful one-stop-shop for common date functions: http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/3/25/some-common-date-routines.aspx.

Be careful not all weeks of all locales start on Monday..

Check this out

set language english

select datepart(dw , dateadd(wk, datediff(wk, 0, getdate()), 0) )

set language italian

select datepart(dw , dateadd(wk, datediff(wk, 0, getdate()), 0) )

I found this one posted by one of our Guru in one thread..

WayneS SSC Guru Points: 95460 More actions · Answer 4

ColdCoffee (8/2/2010)
Be careful not all weeks of all locales start on Monday..

So, you just need to get @@datefirst, and adjust accordingly. Something like:

declare @DateFirst int

set @DateFirst = @@datefirst

SET DATEFIRST 1

-- do your calculation

SET DATEFIRST @DateFirst

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

WayneS (8/2/2010)
ColdCoffee (8/2/2010)
Be careful not all weeks of all locales start on Monday..
So, you just need to get @@datefirst, and adjust accordingly. Something like:
declare @DateFirst int
set @DateFirst = @@datefirst
SET DATEFIRST 1
-- do your calculation
SET DATEFIRST @DateFirst

Yes, u are right, that's a way to solve this DATEFIRST issue! Thanks, Wayne!

2Tall SSChampion Points: 14644 More actions · Answer 6

Thanks for all your efforts.

My query comprises of 3 CTE's then a UNION ALL

I have posted my code. Apologies in advance as my programming skills are fairly basic 🙂

I have added 2 lines to my code, one in each select as part of the UNION ALL:

dateadd(wk, datediff(wk, 0, i.DueDate), 0) AS Week,

dateadd(wk, datediff(wk, 0, p.DueDate), 0) AS Week,

I was unsure how to implement Wayne's suggestion within my code:

declare @DateFirst int

set @DateFirst = @@datefirst

SET DATEFIRST 1

-- do your calculation

SET DATEFIRST @DateFirst

I would post sample data as with previous posts but so many tables are invloved.....

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

LutzM SSC Guru Points: 107049 More actions · Answer 7

ColdCoffee (8/2/2010)
jvanderberg (8/2/2010)
dateadd(wk, datediff(wk, 0, @ThisDate), 0)
This will convert a date into the first Monday of the week that contains it. Group on this date.
Check out Lynn Pettis' wonderful one-stop-shop for common date functions: http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/3/25/some-common-date-routines.aspx.
Be careful not all weeks of all locales start on Monday..
Check this out
set language english
select datepart(dw , dateadd(wk, datediff(wk, 0, getdate()), 0) )
set language italian
select datepart(dw , dateadd(wk, datediff(wk, 0, getdate()), 0) )
I found this one posted by one of our Guru in one thread..

Those are two separate issues...

The first statement dateadd(wk, datediff(wk, 0, @ThisDate), 0) will always return the date for Monday of the current week, where "current week" starts on Sunday. This is regardless of LANGUAGE or DATEFIRST setting.

If you want to start the week always on Monday, change the statement to

dateadd(wk, datediff(wk, 0, @ThisDate - 1), 0)

This will always return the date for Monday, starting the week on Monday.

The second set of statements select datepart(dw, dateadd(wk, datediff(wk, 0, getdate()), 0) ) ... is DATEFIRST sensitive, since it uses DATEPART(dw). As per BOL DATEPART(wk) and DATEPART (dw) depends on the value set by SET DATEFIRST.

If you want the day of week starting on Monday, you could use SELECT DATEDIFF(dd,'1900-01-01',GETDATE()) % 7 + 1.

Whenever possible I try to avoid messing around with SET DATEFIRST {EDIT:} and DATEPART()...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

2Tall SSChampion Points: 14644 More actions · Answer 8

As usual great work guys.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper