Need to count records grouped in 5 min intervals

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)

Need to count records grouped in 5 min intervals

Author

Topic

mattd_nz
Starting Member

6 Posts

Posted - 2006-03-22 : 22:03:14

Hi
I have a table full of records loaded with a time stamp in a standared datetime field called start_time;

2006-01-22 00:00:12.000

Over a period of 24 hrs I want to pull out something that will look roughly like:

Time Record Count
18:00 457
18:05 562
18:10 377

etc.

So far the best I've come up with is this..... Any help moving forward would be gratefully accepted.

select count(*) CdrsIn5Min from cdrtable
where start_time between dateadd(mi,-5,'2006-03-22 10:00:00') and '2006-03-22 10:00:00'

CdrsIn5Min
-----------
528

(1 row(s) affected)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 22:21:37

[code]declare @table table
(
time_stamp datetime
)

insert into @table
select '2006-03-23 18:00:00' union all
select '2006-03-23 18:01:00' union all
select '2006-03-23 18:02:00' union all
select '2006-03-23 18:02:10' union all
select '2006-03-23 18:03:00' union all
select '2006-03-23 18:04:00' union all
select '2006-03-23 18:05:00' union all
select '2006-03-23 18:06:00' union all
select '2006-03-23 18:06:10' union all
select '2006-03-23 18:06:20' union all
select '2006-03-23 18:07:00' union all
select '2006-03-23 18:08:00' union all
select '2006-03-23 18:10:02' union all
select '2006-03-23 18:10:04' union all
select '2006-03-23 19:00:05'

select dateadd(minute, datediff(minute, 0, time_stamp) / 5 * 5, 0) as [Time], count(*) as [Record Count]
from @table
group by dateadd(minute, datediff(minute, 0, time_stamp) / 5 * 5, 0)[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

mattd_nz
Starting Member

6 Posts

Posted - 2006-03-22 : 22:52:17

Ace, thanks a lot. Reports are now winging their happy way to management :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources