Problem with group by week

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 'W Starting'
to do my select & group by
This brings back
W 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 "W Starting":
W 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
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 w start of a Sunday (@.@.DateFirst). 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 w.(murtagh@.surfanytime.co.uk) writes:
> DateADD("wk",datediff("wk",0,TA_DATE),0) as 'W Starting'
> to do my select & group by
> This brings back
> W 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 "W Starting":
> W 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
> 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 w start of a Sunday (@.@.DateFirst). Is there anyway to
> override this ' or has anyone else ever seem this issue.
SET DATEFIRST could help you, although I would rather write something
that looked at @.@.datefirst.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I think that this may be caused by our sql DB set up
> with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
> override this ' or has anyone else ever seem this issue.
You can change the starting day to Monday using DATEFIRST:
SET DATEFIRST 1
GO
SELECT DATEADD(wk, DATEDIFF(wk,0,'20051105'),0) as 'W Starting'
SELECT DATEADD(wk, DATEDIFF(wk,0,'20051106'),0) as 'W Starting'
Hope this helps.
Dan Guzman
SQL Server MVP
<murtagh@.surfanytime.co.uk> wrote in message
news:1131363682.312830.174660@.z14g2000cwz.googlegroups.com...
> 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 'W Starting'
> to do my select & group by
> This brings back
> W 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 "W Starting":
> W 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
> 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 w start of a Sunday (@.@.DateFirst). 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 w.
>|||I would avoid anything that relies on DATEFIRST or regional
settings, and instead, define the w 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 w (note that January 1, 1900
was a Monday):
-- Group by ws starting on Mondays
select
count(OrderID) as numOrders,
min(dateadd(day, datediff(day,'19000101',OrderDate)/7*7, '19000101'))
as WStarting,
min(OrderDate) as EarliestOrder,
max(OrderDate) as LatestOrder
from Northwind..Orders
group by datediff(day,'19000101',OrderDate)/7
order by WStarting
-- Group by ws starting on Tuesdays
select
count(OrderID) as numOrders,
min(dateadd(day, datediff(day,'19000102',OrderDate)/7*7, '19000102'))
as WStarting,
min(OrderDate) as EarliestOrder,
max(OrderDate) as LatestOrder
from Northwind..Orders
group by datediff(day,'19000102',OrderDate)/7
order by WStarting
If you need something more efficient, there are other options using
a calendar table. See http://www.aspfaq.com/show.asp?id=2519.
A calendar table used with an outer join can also provide result
rows for ws in which your table has no data.
Steve Kass
Drew University
murtagh@.surfanytime.co.uk wrote:

>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 'W Starting'
>to do my select & group by
>This brings back
>W 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 "W Starting":
>W 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
>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 w start of a Sunday (@.@.DateFirst). 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 w.
>
>|||Hi Dan
unfortunally, changing the datefirst defaults that permission to all
users. Which could just screw someone else up, even if I change it back
to Sunday after I have finished. Surely there has to be a better way
............. hmmmmmm
thanks.|||SET DATEFIRST only affects the current connection.
Erland and Steve mentioned alternatives to SET DATEFIRST that I should have
mentioned. Personally, I'd go with the calendar table. It can be used for
other date related functions as well, such as fiscal years, holidays, etc.
Hope this helps.
Dan Guzman
SQL Server MVP
<murtagh@.surfanytime.co.uk> wrote in message
news:1131372648.404640.99490@.g43g2000cwa.googlegroups.com...
> Hi Dan
> unfortunally, changing the datefirst defaults that permission to all
> users. Which could just screw someone else up, even if I change it back
> to Sunday after I have finished. Surely there has to be a better way
> ............. hmmmmmm
> thanks.
>|||Thanks guys
Going to go with Steve's solution and will come back later to look at
the calender table, unfortunally I have sunk a lot of time into this
'*little*' problem trying to work it out on my own.
Thanks again
Frank
"A guy who though he knew everything but actually knows nothing" - he
he