MS SQL Query to count records within 5 minute intervals

I'm looking for a MS SQL query for SQL 2000 where I can count records within 5 minute intervals.  Once I have this data I'm going to use it to chart the numbers so I will want to write it to a new table and be able to go backward through my existing data.  Additionally I want to do this going forward, presumably with DTS.?

Thanks in advance.
LVL 7
powercramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
This will show you how you can round time down to 5 minute increments:




declare @DT datetime
set @dt=getdate()
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,1,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,4,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,7,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt

Open in new window

0
Daniel WilsonCommented:
Select  DateDiff(m, '1/1/2000', MyDatefield) / 5, count(DateDiff(m, '1/1/2000', MyDatefield) / 5)
from MyTable
Group by DateDiff(m, '1/1/2000', MyDatefield) / 5
0
BrandonGalderisiCommented:
DW: m is for month :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BrandonGalderisiCommented:
mi or n is for minute
0
Daniel WilsonCommented:
You're right ... corrected in the one I tested ... failed to correct in what I posted.  So ... if he prefers my solution ...

Select  DateDiff(n, '1/1/2000', MyDatefield) / 5, count(DateDiff(n, '1/1/2000', MyDatefield) / 5)
from MyTable
Group by DateDiff(n, '1/1/2000', MyDatefield) / 5

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
powercramAuthor Commented:
DanielWilson,

Sorry for my ignorance but how would I name the columns so I can do an order by?
0
powercramAuthor Commented:
I'm guessing the number in the first column is the time relative to 1900 or whatever?  How can I convert that back to the actual date/time?
0
BrandonGalderisiCommented:
The one that I have does that.  It gives you the time.
0
BrandonGalderisiCommented:
select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0), count(*)
from YourTable
group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
0
powercramAuthor Commented:
Brandon,

This is all I get with yours.
Untitled.png
0
BrandonGalderisiCommented:
That was just an example of how it would round various times (the right column) down to the 5 minute increment.
0
powercramAuthor Commented:
This one is very close to what I'm after.

     select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0), count(*)
     from YourTable
     group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
     order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)

It is only displaying the time periods where a record exists.  How can I get it to display the period even if it is zero (IE has no entry)?
0
BrandonGalderisiCommented:
Are you on SQL 2000 or 2005?
0
powercramAuthor Commented:
SQL 2000.  I'm using SQL Server 2005 Management Studio.
0
BrandonGalderisiCommented:
In order to do that, you need to have a time coordinate table.  We can fake that though if you have a numbers table.  If you have neither, we would have to generate numbers in the procedure and it would not be efficient.  Do you have a numbers table?

ie. a table that contains sequential numbers.
0
powercramAuthor Commented:
I do have a numbers table.  Currently it only goes to 9000.
0
powercramAuthor Commented:
I think what we have will work for my purpose.  If you have time additional info on getting the 0 items would be nice but not necessary.

Thank you both for your (quick) help!
0
powercramAuthor Commented:
Thank you both very much!
0
BrandonGalderisiCommented:
You'll need more than 9000.  Basically you'll need n numbers where n is the number of 5 minute increments you will have from your earliest to your latest data point.

The basic principal is.
declare @minD datetime
@maxD datetime
select @mind = dateadd(n, (datediff(n, 0,min(YourDateColumn))/5)*5,0)
, @maxd = dateadd(n, (datediff(n, 0,max(YourDateColumn))/5)*5,0)
 
--To see the approximate numbers you need as of NOW, not counting the 288 increments you will need per day.
 
select datediff(d,@mind,@max)*288
 
 
 
--Your list of ALL dates would then be
 
select dateadd(n, (n-1)*5,@minD)
from YourNumbersTable
where dateadd(n, (n-1)*5,@minD) <= @MaxD
 
--and incorporated into your above query.  I'm using inc to refer to an increment (of 5).
 
select allinc.theInc,isnull(cnt,0) as Count
(select dateadd(n, (n-1)*5,@minD) theInc
from YourNumbersTable
where dateadd(n, (n-1)*5,@minD) <= @MaxD) as AllInc
left outer join 
(select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0) theinc, count(*) cnt
from YourTable
group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)) ThisInc
on allinc.theinc = thisinc.theinc

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.