Group by week issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group by week issue

Author

Topic

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 05:37:29

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 05:40:47

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 06:02:53

The data that is comming back when I query the DB is :

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

but this should look like, if the DB was grouping the data correctly under "Week Starting":

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
31/10/2005 00:00:00 M&E 06/11/2005 54
07/11/2005 00:00:00 M&E 07/11/2005 24

As the 06/11/2005 is part of week begining 31/10/2005 (Mon - Sunday 31/10/2005 to 06/11/2005)

This seems to happen for all Mondays that I bring back, so instead of the DB grouping by : DateADD("wk",datediff("wk",0,TA_DATE),0) as 'Week Starting' which is the Monday of each week , it will override this grouping on select and group by the SQL Server Default for the first day of the week which in the case of our server is Sunday.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 06:18:03

Try this

DateADD("wk",datediff("wk",0,TA_DATE),1) as 'Week Starting'

Madhivanan

Failing to plan is Planning to fail

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 06:36:04

Hi Madhivanan

All that will do is change the First Day of the Week to Tue as opposed to Monday which is what I want. The issure is not with getting the first day of the week as DateADD("wk",datediff("wk",0,TA_DATE),0) gets this correctly (Monday). The issue is the grouping where despite stating that I want to group with Monday being the First Day of the Week, SQL Server insists on Grouping it with Sunday being the first day of the week and here is with the problem lies

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-07 : 07:38:16

I think you need to use DateFirst
Refer Books on Line, SQL Server help file for more information

Madhivanan

Failing to plan is Planning to fail

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 07:58:07

Hi Madhivanan

While that seems an interesting solultion, it cannot be done as it is set to Sunday for a reason !!

Thank you anyway

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-07 : 08:39:02

Group your result this way:
dateadd(dd,(datediff(dd,-53690,a.TA_DATE)/7)*7,-53690)

It is independent of the DATEFIRST setting, and will give you the Monday on or before any datatime value.

This topic may be useful for what you want to do:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 09:44:53

Hi Micheal,

I went with this instead, but just seeing yours will also have a look at it for over head.

-- Group by weeks starting on Mondays - Using Northwind
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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-07 : 10:07:59

That should work OK for you, provided that you keep in mind that this will return incorrect results for dates before 19000101.
dateadd(day, datediff(day,'19000101',OrderDate)/7*7, '19000101')

That is why I use the following, because -53690 = '1753/01/01', the earliest possible datetime in SQL Server. The algorithm works for all possible SQL Server datetime values.
dateadd(dd,(datediff(dd,-53690,a.TA_DATE)/7)*7,-53690)

CODO ERGO SUM

Digambar
Starting Member

1 Post

Posted - 2009-08-15 : 08:58:01

Thanks! I was too unable to group by week although I was grouping by first day of week (Monday). Your solution just worked. Thanks a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources