SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Group By using Weeks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/29/2000 :  11:17:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 10/20/2000 :  13:40:29  Show Profile  Reply with Quote
Group By using Weeks

You might also consider DATEPART(ww, @ADateValue) for this.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/23/2000 :  02:52:47  Show Profile  Reply with Quote
help

when ever ur info it is not upto the mark i.e i want complete infor about what requesting.
ur site is best

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 12/23/2000 :  03:55:09  Show Profile  Reply with Quote
help

when ever ur info it is not upto the mark i.e i want complete infor about what requesting.
ur site is best

Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/05/2001 :  15:59:05  Show Profile  Visit jcelko's Homepage  Reply with Quote
Set up a Calendar table for your system, which has the calendar dates and columns which tell you the fiscal year, if it is a holiday or not, etc. And one of these columns will be the ISO week number (not the same as the week-within-year that MS gives you). Your query is then something like this:

SELECT C1.week_nbr, AVG(score)
FROM Foobar AS F1, Calendar AS C1
WHERE F1.test_date = C1.cal_date
GROUP BY C1.week_nbr;

--CELKO--
Joe Celko, SQL Guru
Go to Top of Page

TheNephalim
Starting Member

1 Posts

Posted - 02/06/2002 :  11:58:05  Show Profile  Reply with Quote
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.

Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 05/17/2002 :  16:48:15  Show Profile  Reply with Quote
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.




Go to Top of Page

Bill Wilkinson
Starting Member

USA
7 Posts

Posted - 05/17/2002 :  16:52:05  Show Profile  Reply with Quote
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!)



Go to Top of Page

durgasubburaman
Starting Member

India
2 Posts

Posted - 03/26/2003 :  07:40:46  Show Profile  Send durgasubburaman a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

wong_mz
Starting Member

1 Posts

Posted - 07/27/2005 :  02:53:18  Show Profile  Reply with Quote
The sql don't seem to work. Is this suitable for all versions?
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 08/10/2005 :  17:49:13  Show Profile  Reply with Quote
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".
Go to Top of Page

murtagh
Starting Member

7 Posts

Posted - 11/07/2005 :  04:55:29  Show Profile  Reply with Quote
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.
Go to Top of Page

Mesktomten
Starting Member

Sweden
8 Posts

Posted - 01/19/2006 :  07:07:01  Show Profile  Reply with Quote
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

Go to Top of Page

murtagh
Starting Member

7 Posts

Posted - 01/19/2006 :  07:53:47  Show Profile  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 01/19/2006 :  08:23:28  Show Profile  Reply with Quote
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.

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/19/2006 :  11:21:15  Show Profile  Reply with Quote
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
Go to Top of Page

TimP
Starting Member

USA
1 Posts

Posted - 10/15/2010 :  16:40:33  Show Profile  Reply with Quote
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)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000