Frequency per minute sql query

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

Transact-SQL (2005)

Frequency per minute sql query

Author

Topic

Nroblex
Starting Member

17 Posts

Posted - 2010-01-22 : 08:42:49

Hello!

I have a large table with many columns and one of the columns contain a datetime value, that indicates when the record was inserted in the table e.g a GetDate() value.

Now I want to construct a smart SQL query that can give me information about how many records that are inserted per minute within a given interval

For Example:

Between 2010-01-21 06:30:00 AND 2010-01-21 09:30:00 there are 4600 new records inserted, I now want to have an answer for the records inserted for every minute like this:

2010-01-21 06:30:00 to 2010-01-21 06:31:01 -> 25 records
2010-01-21 06:31:00 to 2010-01-21 06:32:01 -> 29 records
2010-01-21 06:32:00 to 2010-01-21 06:33:01 -> 27 records

....

Many thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-22 : 09:03:06

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Col1), 0) AS theTime,
COUNT(*)
FROM Table1
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Col1), 0)
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, Col1), 0)

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 09:04:43

well -- this would probably work.

SELECT
DATEADD(MINUTE, -1, [strippedTime]) AS [From]
, [strippedTime] AS [To]
, COUNT([strippedTime]) AS [entries]
FROM
(
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, [timeStamp]), 0), [timeStamp]), DATEADD(DAY, DATEDIFF(DAY, 0, [timeStamp]), 0)) AS [strippedTime]
FROM
#foo
)
st
GROUP BY
DATEADD(MINUTE, -1, [strippedTime])
, [strippedTime]

The line


DATEADD(MINUTE, DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, [timeStamp]), 0), [timeStamp]), DATEADD(DAY, DATEDIFF(DAY, 0, [timeStamp]), 0)) AS [strippedTime]

strips the DATETIME down to minute resolution. Then you can simply group by to get the count of rows with this value

This will give you gaps where there were no records. You could fix that by making a range table with all the possible minute values in it.

Here the table is called #foo and the column is called timestamp

NB -- Use Peso's code -- minute resolution from date 0 is fine all the way up to year 5983!
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION[/red]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 09:07:09

Hi peso -- won't doing the DATEDIFF direct on MINUTE as you suggested run into overflow errors?

It can't handle all the possible values a date would have.

The maximum date is 9999-12-31 and minutes in this overflows an int.

Might not matter in real life though!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-22 : 09:10:01

quote:
Originally posted by Transact Charlie

Hi peso -- won't doing the DATEDIFF direct on MINUTE as you suggested run into overflow errors?

It that's the case, replace 0 with 39750 as parameter.
39750 is October 31, 2008.

You can have any integer value as long as it smaller than the smallest/oldest data.

SELECT DATEADD(DAY, 39750, 0)
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 39750, Col1), 39750) AS theTime,
COUNT(*)
FROM Table1
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 39750, Col1), 39750)
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, 39750, Col1), 39750)

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 09:12:12

It probably won't matter!


SELECT
	DATEADD(MINUTE, 2147483647 , 0)

Results


5983-01-24 02:07:00.000

I'd be surprised if anything I ever do will still be valid in the next few 100 years, let alone the next few thousand!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Nroblex
Starting Member

17 Posts

Posted - 2010-01-22 : 09:19:41

Peso, thank you that works very nice!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources