Grupo de SQL Server por recuento de DateTime por hora?

create table #Events ( EventID int identity primary key, StartDate datetime not null, EndDate datetime not null ) go insert into #Events (StartDate, EndDate) select '2007-01-01 12:44:12 AM', '2007-01-01 12:45:34 AM' union all select '2007-01-01 12:45:12 AM', '2007-01-01 12:46:34 AM' union all select '2007-01-01 12:46:12 AM', '2007-01-01 12:47:34 AM' union all select '2007-01-02 5:01:08 AM', '2007-01-02 5:05:37 AM' union all select '2007-01-02 5:50:08 AM', '2007-01-02 5:55:59 AM' union all select '2007-01-03 4:34:12 AM', '2007-01-03 4:55:18 AM' union all select '2007-01-07 3:12:23 AM', '2007-01-07 3:52:25 AM' 

(con disculpas a http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server para cosechar su sql base)

Estoy tratando de encontrar el recuento de eventos que ocurrieron en una hora, por lo que el conjunto de resultados se vería así:

 2007-01-01 12:00 3 2007-01-02 5:00 2 2007-01-03 4:00 1 2007-01-07 3:00 1 

He estado jugando con dateadd y rondando y agrupando pero no obteniéndolo. ¿Alguien puede ayudar?

Gracias.

¿Qué tal esto? Asumiendo SQL Server 2008:

 SELECT CAST(StartDate as date) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) 

Para pre-2008:

 SELECT DATEADD(day,datediff(day,0,StartDate),0) AS ForDate, DATEPART(hour,StartDate) AS OnHour, COUNT(*) AS Totals FROM #Events GROUP BY CAST(StartDate as date), DATEPART(hour,StartDate) 

Esto resulta en :

 ForDate | OnHour | Totals ----------------------------------------- 2011-08-09 00:00:00.000 12 3 

Alternativamente, simplemente GROUP BY la hora y el día:

 SELECT CAST(Startdate as DATE) as 'StartDate', CAST(DATEPART(Hour, StartDate) as varchar) + ':00' as 'Hour', COUNT(*) as 'Ct' FROM #Events GROUP BY CAST(Startdate as DATE), DATEPART(Hour, StartDate) ORDER BY CAST(Startdate as DATE) ASC 

salida:

 StartDate Hour Ct 2007-01-01 0:00 3 2007-01-02 5:00 2 2007-01-03 4:00 1 2007-01-07 3:00 1 

Encontré esto en otro lugar. Me gusta esta respuesta!

 SELECT [Hourly], COUNT(*) as [Count] FROM (SELECT dateadd(hh, datediff(hh, '20010101', [date_created]), '20010101') as [Hourly] FROM table) idat GROUP BY [Hourly]