Separating Data Into One Week Buckets

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 2000 Forums

Transact-SQL (2000)

Separating Data Into One Week Buckets

Author

Topic

gad
Starting Member

14 Posts

Posted - 2006-02-17 : 14:38:34

I have a table with a date field:

2006-01-08 23:00:25.000
2006-01-08 23:00:24.000
2006-01-08 23:00:24.000
2006-01-08 23:00:25.000
2006-01-08 23:00:25.000
2006-01-08 23:00:25.000
etc.

And I'd like to take a count of the number of records that exist within in a 7 day period (one week buckets). Any help would be appreciated. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 15:16:51

Here's one way:


select	dateadd(day, (datediff(day, 0, dt)/7)*7, 0) as [7DayPeriodStarting]
	,count(*) as [RowCount]
from	(
	select '2006-01-08 23:00:25.000' dt union all
	select '2006-01-08 23:00:24.000' union all
	select '2006-01-08 23:00:24.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' 
	) a
group by dateadd(day, (datediff(day, 0, dt)/7)*7, 0)

output:
7DayPeriodStarting                                     RowCount    
------------------------------------------------------ ----------- 
2006-01-02 00:00:00.000                                6
2006-02-06 00:00:00.000                                3

Another way would be to incorporate a Numbers or Calandar table. That might give you more control on exactly what days each bucket should cover. The above method only returns "buckets" that have rows.

EDIT:
a simpler form of the (above) code would just be this the following. I guess you should post your desire output.


select	datediff(day, 0, dt)/7 as [BucketID]
	,count(*) as [RowCount]
from	(
	select '2006-01-08 23:00:25.000' dt union all
	select '2006-01-08 23:00:24.000' union all
	select '2006-01-08 23:00:24.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-01-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' union all
	select '2006-02-08 23:00:25.000' 
	) a
group by datediff(day, 0, dt)/7

output:
BucketID    RowCount    
----------- ----------- 
5531        6
5536        3

Be One with the Optimizer
TG

gad
Starting Member

14 Posts

Posted - 2006-02-17 : 19:48:11

Thanks - this works well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources