group by week problem

Question

group by week problem

Bob hopw

SSC Journeyman

Points: 90
More actions
November 7, 2005 at 3:41 am

#92079

Hi

Is there some internal setting within SQL Server that can override your group by date ?? I have a query that I am trying to group by a Monday and I am using :
DateADD("wk",datediff("wk",0,TA_DATE),0) as 'Week Starting'
to do my select & group by
This brings back
Week Starting Contr dDate Fault
31/10/2005 00:00:00 M&E 05/11/2005 94
31/10/2005 00:00:00 M&E 30/10/2005 18
31/10/2005 00:00:00 M&E 31/10/2005 93
07/11/2005 00:00:00 M&E 06/11/2005 54
07/11/2005 00:00:00 M&E 07/11/2005 24
Monday is the 7th which is correct. but then it grouped the 06/11/2005 ( Which is the Sunday) under the 7th as well but it should be under the 31st Mon - Sun. I think that this may be caused by our sql DB set up with a default week start of a Sunday (And I cannot get a get a straight ans from DBA). Is there anyway to override this ?? or has anyone else ever seem this issue.
Any help appreciated, as I have been knocking my head off it for the last week.

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I think the problem is with your assumption that the system is set to have Sunday be the LAST day of the week and NOT the FIRST day.

Try running this statement

SELECT DATEPART(WEEKDAY, '2005-11-06') If it returns a 1 then the system is set with Sunday as the beginning of the week.

Then try the below

SET DATEFIRST 1

SELECT DATEPART(WEEKDAY, '2005-11-06')

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Bob hopw SSC Journeyman Points: 90 More actions · Answer 2

Hi AJ

using select @@DATEFIRST brings me back 7 - for Sunday and

SELECT DATEPART(WEEKDAY, '2005-11-06') brings back a 1,

quick question if I use SET DATEFIRST 1 will that not change the default date for all users on the DB to Monday (Don't really want to do this) or will it only change it for the session logging in ??

Thanks

Frank

Bob hopw SSC Journeyman Points: 90 More actions · Answer 3

Hi AJ

just looked set DateFirst sets :

Permissions

SET DATEFIRST permissions default to all users.

So I don't really want to go there, any other ideas ?

Thanks again

Frank

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

Try running the below in query pane 1

SET DATEFIRST 1

SELECT DATEPART(WEEKDAY, '2005-11-06')

And this in query pane 2

SELECT DATEPART(WEEKDAY, '2005-11-06')

The DateFirst only affects current execution and does not update the server...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 5

SELECT (((8 - @@DATEFIRST) % 7) + 1)

It's a mouthful but it will give you the weekday Monday falls on based on whatever the @@DATEFIRST setting is.

Just plug that in to your code wherever you need to identify the first day.

Bob hopw SSC Journeyman Points: 90 More actions · Answer 6

Thanks Paul,

but I went with this instead, avoid anything that relies on DATEFIRST or regional

settings, and instead, define the week start explicitly in the query.

Here is one solution (using the sameple database Northwind)

that does not use any auxiliary tables, shown with both Monday

and Tuesday as the first day of the week (note that January 1, 1900

was a Monday):

-- Group by weeks starting on Mondays

select

count(OrderID) as numOrders,

min(dateadd(day, datediff(day,'19000101',OrderDate)/7*7, '19000101'))

as WeekStarting,

min(OrderDate) as EarliestOrder,

max(OrderDate) as LatestOrder

from Northwind..Orders

group by datediff(day,'19000101',OrderDate)/7

order by WeekStarting

-- Group by weeks starting on Tuesdays

select

count(OrderID) as numOrders,

min(dateadd(day, datediff(day,'19000102',OrderDate)/7*7, '19000102'))

as WeekStarting,

min(OrderDate) as EarliestOrder,

max(OrderDate) as LatestOrder

from Northwind..Orders

group by datediff(day,'19000102',OrderDate)/7

order by WeekStarting

Thanks again

Frank

Sergiy SSC Guru Points: 110208 More actions · Answer 7

(@@Datefirst + DATEPART(WEEKDAY, @AnyDate) )%7

is constant for any DATEFIRST set up.

For Mon it's 2, fot Tue it's 3, etc.

Use it to get rid of DATEFIRST dependency.

If you use

(@@Datefirst + DATEPART(WEEKDAY, @AnyDate) -2 )%7 + 1

you always get same values as when DATEFISRT = 1.

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 8

Perfect, Sergiy! I was trying to find a way how to do that, not long ago... and gave up in the end - didn't think about using modulo. I solved it using SET DATEFIRST, but there are situations where this can't be used. I'm sure I'll have opportunity to use this piece of code sooner or later.

Thanks!