| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
 Posted - 06/05/2001 : 15:59:05
|
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
|
 |
|
|
TheNephalim
Starting Member
1 Posts |
Posted - 02/06/2002 : 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
USA
7 Posts |
Posted - 05/17/2002 : 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
USA
7 Posts |
Posted - 05/17/2002 : 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
India
2 Posts |
Posted - 03/26/2003 : 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 Posts |
Posted - 07/27/2005 : 02:53:18
|
| The sql don't seem to work. Is this suitable for all versions? |
|
|
|
nosepicker
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 08/10/2005 : 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 - 11/07/2005 : 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
Sweden
8 Posts |
Posted - 01/19/2006 : 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 - 01/19/2006 : 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
United Kingdom
1961 Posts |
Posted - 01/19/2006 : 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)
USA
6997 Posts |
Posted - 01/19/2006 : 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
USA
1 Posts |
Posted - 10/15/2010 : 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)
|
|
|
| |
Topic |
|
|
|
Article: Group By using Weeks
