?
Solved

MS SQL Query to count records within 5 minute intervals

Posted on 2008-10-24
19
Medium Priority
?
709 Views
Last Modified: 2011-10-19
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.
0
Comment
Question by:powercram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
19 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800013
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22800014
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800029
DW: m is for month :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800032
mi or n is for minute
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 22800071
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
 
LVL 7

Author Comment

by:powercram
ID: 22800105
DanielWilson,

Sorry for my ignorance but how would I name the columns so I can do an order by?
0
 
LVL 7

Author Comment

by:powercram
ID: 22800121
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800122
The one that I have does that.  It gives you the time.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1000 total points
ID: 22800128
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
 
LVL 7

Author Comment

by:powercram
ID: 22800151
Brandon,

This is all I get with yours.
Untitled.png
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800166
That was just an example of how it would round various times (the right column) down to the 5 minute increment.
0
 
LVL 7

Author Comment

by:powercram
ID: 22800198
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800205
Are you on SQL 2000 or 2005?
0
 
LVL 7

Author Comment

by:powercram
ID: 22800217
SQL 2000.  I'm using SQL Server 2005 Management Studio.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800226
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
 
LVL 7

Author Comment

by:powercram
ID: 22800257
I do have a numbers table.  Currently it only goes to 9000.
0
 
LVL 7

Author Comment

by:powercram
ID: 22800377
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
 
LVL 7

Author Closing Comment

by:powercram
ID: 31509821
Thank you both very much!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800589
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question