COUNT every 5 minutes on a Date_Time field

[eyalkz]

I'm trying to write SQL query on SQL Server,
I need to count number of different requests (on a requests field)
every 5 minutes
The 5 minutes interval is supposed to be calculated on a Date_Time field
that contains years,months,days,hours,minutes,seconds...
Thanks
Eyal

 

[tlbroadbent]

Try this. Change the column and table names.

Select
Interval=(datepart(minute, yourcolumn) / 5) + 1,
Cnt=count(*)
From YourTable
Group By (datepart(minute, yourcolumn) / 5) + 1 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[MeanGreen]

I think this will get you what you want:

create table #test (col1 varchar(25), event datetime)

insert into #test values ('1','1/1/2003 12:05')
insert into #test values ('1','1/1/2003 12:07')
insert into #test values ('1','1/1/2003 12:15')
insert into #test values ('1','1/1/2003 12:08')
insert into #test values ('1','1/1/2003 12:35')
insert into #test values ('1','1/1/2003 12:45')
insert into #test values ('1','1/1/2003 12:22')
insert into #test values ('1','1/1/2003 12:32')
insert into #test values ('1','1/1/2003 14:32')

select col1,datepart(hh,event) TheHour,(datepart(mi,event)/5 ) * 5 TheMinute, count(*)
from #test
group by col1,datepart(hh,event),(datepart(mi,event)/5 ) * 5
order by col1,datepart(hh,event),(datepart(mi,event)/5 ) * 5

Hope this helps.