group dates by 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

SQL Server 2005 Forums

Transact-SQL (2005)

group dates by weeks

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 08:39:56

hi ,

say I had a table with dates in the format yyyy-mm-dd, how can I group so the first monday to sunday of the month is week 1, second monday to sunday is week 2 and so on ?

so instead of displaying my values like :

date count
2007-11-05 10
2007-11-06 20
2007-11-07 25
2007-11-08 21
2007-11-09 11
2007-11-10 12
2007-11-11 1
2007-11-12 2
2007-11-13 23

would be like :
weeks count
2007-11-05 to 2007-11-11 99
2007-11-12 to 2007-11-18 25

kindest regards,
jmiae

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 08:46:42

KH
[spoiler]Time is always against us[/spoiler]

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 08:59:34

as always, Amazing stuff ! cheers.

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 09:40:47

khtan, this is really great, but how can I display the week number in another column, so i says:
week
1
2
3
4
1
2
3
4

next to the correct dates ? ie, week 1 is the first monday of the month..
I need to do this so I can compare all week 1's , etc.

thank you.
jamie

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 09:54:37

That depends on what date is week 1. 1st January of the year? does your data span more than one year? if so do you want to group all week 1's regardless of year?

I did an article on this kind of issue here:
[url]http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month[/url]

;-]... Quack Waddle

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 09:56:41

I would want to put in date values, but potentially I would want to graph all week 1's in a year.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 09:59:34

you can also take a look here see if MVJ has one ready http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

KH
[spoiler]Time is always against us[/spoiler]

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:10:53

thanks guys, I'll check it out

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:15:50

Are you just after a sequential row number ?


DECLARE @sample TABLE
(	
	[date] 	datetime,
	[COUNT]	int
)
INSERT INTO @sample
SELECT	'2007-11-05', 10 UNION ALL
SELECT	'2007-11-06', 20 UNION ALL
SELECT	'2007-11-07', 25 UNION ALL
SELECT	'2007-11-08', 21 UNION ALL
SELECT	'2007-11-09', 11 UNION ALL
SELECT	'2007-11-10', 12 UNION ALL
SELECT	'2007-11-11', 1  UNION ALL
SELECT	'2007-11-12', 2  UNION ALL
SELECT	'2007-11-13', 23

SELECT	Row as Weekno, date_start,
	date_end = DATEADD(DAY, 6, date_start),
	total_count
FROM
	(SELECT  ROW_NUMBER() OVER (ORDER BY date_start ASC)
             AS Row, date_start, total_count FROM 
		(
			SELECT	date_start = DATEADD(week, DATEDIFF(week, 0, [date]), 0), 
				total_count = SUM([COUNT])
			FROM	@sample
			GROUP BY DATEADD(week, DATEDIFF(week, 0, [date]), 0)
		) d
	) dwithrownums

Weekno               date_start              date_end                total_count
-------------------- ----------------------- ----------------------- -----------
1                    2007-11-05 00:00:00.000 2007-11-11 00:00:00.000 99
2                    2007-11-12 00:00:00.000 2007-11-18 00:00:00.000 26

(2 row(s) affected)

Don't know what performance might be like on larger dataset, I haven't used ROW_NUMBER() much. Well not at all before today :)

;-]... Quack Waddle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 10:18:22

quote:
Originally posted by jamie

khtan, this is really great, but how can I display the week number in another column, so i says:
week
1
2
3
4
1
2
3
4

next to the correct dates ? ie, week 1 is the first monday of the month..
I need to do this so I can compare all week 1's , etc.

thank you.
jamie

Actually you wanted to group by 1st monday of the month ?

KH
[spoiler]Time is always against us[/spoiler]

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:20:34

Gah I can't read, I see now you want week number per month !!!!

;-]... Quack Waddle

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:26:42

does your data span more than one year? if so do you want to group all week 1's regardless of year?

anwser = yes.

I can't find any sql that does what I want to do.

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:36:06

yeah, I really need the week numbers for each month, so august weeks 1 to 4, sept weeks 1 to 4.
sorry.

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:40:39

So what you gonna compare to October week 5? :)

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:49:26

[code]
DECLARE @sample TABLE
(
[date] datetime,
[COUNT] int
)
INSERT INTO @sample
SELECT '2007-11-05', 10 UNION ALL
SELECT '2007-11-06', 20 UNION ALL
SELECT '2007-11-07', 25 UNION ALL
SELECT '2007-11-08', 21 UNION ALL
SELECT '2007-11-09', 11 UNION ALL
SELECT '2007-11-10', 12 UNION ALL
SELECT '2007-11-11', 1 UNION ALL
SELECT '2007-11-12', 2 UNION ALL
SELECT '2007-11-13', 23 UNION ALL
SELECT '2007-11-14', 23 UNION ALL
SELECT '2007-11-15', 23 UNION ALL
SELECT '2007-11-16', 23 UNION ALL
SELECT '2007-11-17', 23 UNION ALL
SELECT '2007-11-18', 23 UNION ALL
SELECT '2007-11-19', 23 UNION ALL
SELECT '2007-11-20', 23 UNION ALL
SELECT '2007-11-21', 23 UNION ALL
SELECT '2007-11-22', 23 UNION ALL
SELECT '2007-11-23', 23 UNION ALL
SELECT '2007-11-24', 23 UNION ALL
SELECT '2007-11-25', 23 UNION ALL
SELECT '2007-11-26', 23 UNION ALL
SELECT '2007-11-27', 23 UNION ALL
SELECT '2007-11-28', 23 UNION ALL
SELECT '2007-11-29', 23 UNION ALL
SELECT '2007-11-30', 23 UNION ALL
SELECT '2007-12-01', 23 UNION ALL
SELECT '2007-12-02', 23

SELECT
weekno = (day(date_start)+6)/7,
date_start,
date_end = DATEADD(DAY, 6, date_start),
total_count
FROM
(
SELECT date_start = DATEADD(week, DATEDIFF(week, 0, [date]), 0),
total_count = SUM([COUNT])
FROM @sample
GROUP BY DATEADD(week, DATEDIFF(week, 0, [date]), 0)
) d

weekno date_start date_end total_count
----------- ----------------------- ----------------------- -----------
1 2007-11-05 00:00:00.000 2007-11-11 00:00:00.000 99
2 2007-11-12 00:00:00.000 2007-11-18 00:00:00.000 118
3 2007-11-19 00:00:00.000 2007-11-25 00:00:00.000 161
4 2007-11-26 00:00:00.000 2007-12-02 00:00:00.000 161
1 2007-12-03 00:00:00.000 2007-12-09 00:00:00.000 23

(5 row(s) affected)
[/code]

;-]... Quack Waddle

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:49:31

ah shit, so this is a problem yes ?

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:50:27

ooohhhh cross post !! :)

Look up look up.

;-]... Quack Waddle

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:53:08

Genius ! I'll try it out, nice one

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 10:57:32

wheres the cross post ?

cas_o
Posting Yak Master

154 Posts

Posted - 2007-11-15 : 10:59:29

Ah maybe I didn't use the correct term there, I meant crossed in the post (as in snail mail type post) Meaning we posted at the same time, not in the traditional sense of posting across threads. Hah now my brain is truely fried !!! :)

;-]... Quack Waddle

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-15 : 11:05:25

I get you.
thank you for all your assistance, seems easy when you know how ! still can't get my head round that simple piece of maths to work on the weekno though !/

I have tried placing it into my report and the performance is slow, but not to worry, I'll take a look and see if I can speed up the report somehow.

again, thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources