Solved

# MS SQL Query to count records within 5 minute intervals

Posted on 2008-10-24
657 Views
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.?

0
Question by:powercram
• 9
• 8
• 2

LVL 39

Expert Comment

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

``````declare @DT datetime

set @dt=getdate()

``````
0

LVL 32

Expert Comment

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

DW: m is for month :)
0

LVL 39

Expert Comment

mi or n is for minute
0

LVL 32

Accepted Solution

Daniel Wilson earned 250 total points
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
``````
0

LVL 6

Author Comment

DanielWilson,

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

LVL 6

Author Comment

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

The one that I have does that.  It gives you the time.
0

LVL 39

Assisted Solution

BrandonGalderisi earned 250 total points
from YourTable
0

LVL 6

Author Comment

Brandon,

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

LVL 39

Expert Comment

That was just an example of how it would round various times (the right column) down to the 5 minute increment.
0

LVL 6

Author Comment

This one is very close to what I'm after.

from YourTable

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

Are you on SQL 2000 or 2005?
0

LVL 6

Author Comment

SQL 2000.  I'm using SQL Server 2005 Management Studio.
0

LVL 39

Expert Comment

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 6

Author Comment

I do have a numbers table.  Currently it only goes to 9000.
0

LVL 6

Author Comment

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 6

Author Closing Comment

Thank you both very much!
0

LVL 39

Expert Comment

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

from YourNumbersTable

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

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

order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)) ThisInc

on allinc.theinc = thisinc.theinc
``````
0

## Featured Post

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Read about achieving the basic levels of HRIS security in the workplace.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦