Article: Group By using Weeks - 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

Site Related Forums

Article Discussion

Article: Group By using Weeks

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-09-29 : 11:17:37

Robert writes "I have a database that contains articles that are rated between 0 and 10. I want to show the average score for articles on a week-by-week basis. In other words, I want to output something like this:
Week commencing 27/1/00 Average Score = 3.7/10
Week commencing 3/2/00 Average Score = 5/10
...and so on...

In other situations, I would use GROUP BY but I don't see how I can do that with a range of dates. Can you help???"

Article Link.

TheNephalim
Starting Member

1 Post

Posted - 2002-02-06 : 11:58:05

I could be wrong, but it appears that there is a minor error in the code. I am assuming that you wish to have Sunday be the first day of the week. If that is the case, the you would need the following:

DECLARE @myDate datetime
SET @myDate = DateAdd(day, -1 * DATEPART (dw, '10/02/2002') + 1, '10/02/2002' )

Left as it was, you would get the last day ( Saturday ) of the previous week, not the first day of the current date.

Bill Wilkinson
Starting Member

7 Posts

Posted - 2002-05-17 : 16:48:15

APPLAUSE to TheNephalim for pointing out the bug in the beginning of the week bug.

But how come both the original SQLTeam article and TheNephalim's reply used *multiplication* to make the DatePart value negative??? What's wrong with a simply UNARY minus sign, alone???

Instead of:
DateAdd(day, -1 * DATEPART (dw, datefld) + 1, datefld )

Why not simply:
DateAdd(day, - DATEPART (dw, datefld) + 1, datefld )

Or even more simply:
DateAdd(day, 1 - DATEPART (dw, datefld), datefld )

*****************

I had a very similar problem to solve, but in my case the "datefld" contained *both* dates *and* times. So to get all the dates in the given week grouped properly, I had to "strip off" the time values. Which I did thus:

DateAdd(d, 1 - DATEPART(dw, datefld), CONVERT(datetime, CONVERT(varchar,datefld,112) ) )

How come SQL Server doesn't have the capabilities of poor little old Access, the DateValue and TimeValue functions? (They'd be trivial to implement in SQL Server, since all the engine would need to do would be to zero out one or the other of the two integers that make up a datetime [or one of the two short integers that make up a smalldatetime].) Sigh.

Bill Wilkinson
Starting Member

7 Posts

Posted - 2002-05-17 : 16:52:05

quote:

Group By using Weeks <P>You might also consider DATEPART(ww, @ADateValue) for this.

But all that gives you is a WEEK NUMBER. The grouping is thus right, but then how do you convert the week number *back* to a date for display purposes? It's surely not obvious to the typical report reader that Week number 24 starts on May 12th. (If it even does! My count could easily be off by 1 or 2!)

durgasubburaman
Starting Member

2 Posts

Posted - 2003-03-26 : 07:40:46

Dear friends
It has no function and simple query for weekly report as per month and year. please try it out. we expect ur comments

SELECT SUM(Freight) AS weeklyfrieght
FROM Orders
WHERE (MONTH(OrderDate) = 3 AND YEAR(OrderDate) = 1997)
GROUP BY DATEPART(week, OrderDate)
Yours
R.subburaman
my email id:durgasubbu@sify.com

We can win in the Hunt.

wong_mz
Starting Member

1 Post

Posted - 2005-07-27 : 02:53:18

The sql don't seem to work. Is this suitable for all versions?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-10 : 17:49:13

Going back to the first post in this thread, here is a way to group by the exact date that starts each week:

SELECT DATEADD(wk, DATEDIFF(wk, 6, DateColumn), 6), SUM(SomeColumn)
FROM YourTable
GROUP BY DATEADD(wk, DATEDIFF(wk, 6, DateColumn), 6)
ORDER BY DATEADD(wk, DATEDIFF(wk, 6, DateColumn), 6)

This will assume that Sunday is the first day of the week. If you want the first day to be Monday, for example, change "6" to "0".

murtagh
Starting Member

7 Posts

Posted - 2005-11-07 : 04:55:29

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 under the 7th as well. I think that this may be cause our sql is 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.

Mesktomten
Starting Member

8 Posts

Posted - 2006-01-19 : 07:07:01

Hmm..?

No matter what I do this sucker seems to start counting every Sunday. And I get the same result no matter which of the above "methods" I'm using!

The date-column works fine, but the values in sum-column are "constant"...

SELECT DATEADD(wk, DATEDIFF(wk, 6, date), 6), SUM(quantity)
FROM orders
GROUP BY DATEADD(wk, DATEDIFF(wk, 6, date), 6)
ORDER BY DATEADD(wk, DATEDIFF(wk, 6, date), 6)

Returns: 2006-01-01 / 2541730

----------

select sum(quantity) from orders where date between '20060101' and '20060107'

Returns: 2541730

----------

SELECT DATEADD(wk, DATEDIFF(wk, 7, date), 7), SUM(quantity)
FROM orders
GROUP BY DATEADD(wk, DATEDIFF(wk, 7, date), 7)
ORDER BY DATEADD(wk, DATEDIFF(wk, 7, date), 7)

Returns: 2006-01-02 / 2541730

----------

SELECT DATEADD(wk, DATEDIFF(wk, 0, date), 0), SUM(quantity)
FROM orders
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, date), 0)
ORDER BY DATEADD(wk, DATEDIFF(wk, 0, date), 0)

Returns: 2006-01-02 / 2541730

----------

select sum(quantity) from orders where date between '20060102' and '20060108'

Returns: 2541709

----------

DATEFIRST is set to 1

Can anybody help me to sort this one out?

/Martin

murtagh
Starting Member

7 Posts

Posted - 2006-01-19 : 07:53:47

I have stopped using DATEFIRST and am now using the first calender day within sql server :

- 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

This seems to work fine for me

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-19 : 08:23:28

quote:

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

It's probably of no consequence, but I'd be inclined to use '17530101' (also a Monday!) since otherwise week 0 is 13 days long. But clearly this doesn't matter if you're never going to get dates before 1900.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-19 : 11:21:15

quote:
Originally posted by murtagh

I have stopped using DATEFIRST and am now using the first calender day within sql server :

- 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

This seems to work fine for me

You could use the function on this link for this. You can also select any day of the week that you want to group by. This function uses similar logic to what you are doing, except starting from 1753/1/1, as Arnold suggested. The link also has code for doing this directly in your query if you want to for any day of the week.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

TimP
Starting Member

1 Post

Posted - 2010-10-15 : 16:40:33

Something like this works..

Select
CONVERT(varchar(20), DateAdd(day, -(DatePart(dw, d.DateRequested) - 1), d.DateRequested), 110) + ' thru ' + CONVERT(varchar(20), DateAdd(day, 6, DateAdd(day, -(DatePart(dw, d.DateRequested) - 1), d.DateRequested)), 110), Count(*)

From Table d

Group By
CONVERT(varchar(20), DateAdd(day, -(DatePart(dw, d.DateRequested) - 1), d.DateRequested), 110) + ' thru ' + CONVERT(varchar(20), DateAdd(day, 6, DateAdd(day, -(DatePart(dw, d.DateRequested) - 1), d.DateRequested)), 110)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources