SQL query to give number of records per minute of DATE_TIME

[Harmonitron]

Can anyone help me write a query to give the number of records per minute in the following example.

Source data:
DATE_TIME
12/09/2005 10:37:19
12/09/2005 10:37:21
12/09/2005 10:37:24
12/09/2005 10:38:12
12/09/2005 10:38:17
12/09/2005 10:38:19
12/09/2005 10:38:22
12/09/2005 10:38:53
12/09/2005 10:38:56
12/09/2005 10:39:00
12/09/2005 10:39:03
12/09/2005 10:39:05
12/09/2005 10:39:10

Desired result data:
DATE_TIME Count
12/09/2005 10:37 3
12/09/2005 10:38 6
12/09/2005 10:37 4

I'm want to chart/graph the number of records written per minute.

Many thanks.

 

[gmmastros]

Here's one solution, there may be others...

Select 	DateAdd(Second, -1 * DatePart(second, date_Time), Date_Time),
		Count(DateAdd(Second, -1 * DatePart(second, date_Time), Date_Time)) As Count
from 	<tablename>
Group By DateAdd(Second, -1 * DatePart(second, date_Time), Date_Time)

Basically, subtract the number of seconds from the date_time field and use it for the group by.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Or floor date_time value down to minute... basically the same thing but does not depend on eventual milliseconds:

select dateadd(mi, datediff(mi, 0, date_Time), 0), count(*)
from <tablename>
group by dateadd(mi, datediff(mi, 0, date_Time), 0)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Harmonitron]

Brilliant, George.

Thank you for the very fast response. It does the job beautifully. I dip into SQL now and then for certain applications so you've saved me a lot of time getting up to speed on this.