Gruppo SQL Server per conteggio di DateTime all’ora?

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 scuse a http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server per la raccolta del loro sql di base)

Sto cercando di trovare il numero di eventi che si sono verificati in un’ora, quindi il set di risultati sarebbe simile a questo:

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

Ho giocato con dateadd, round e raggruppamenti ma non l’ho capito. Qualcuno può aiutare?

Grazie.

    Cosa ne pensi di questo? Supponendo 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) 

    Per il 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) 

    Questo risulta in :

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

    In alternativa, solo GROUP BY l’ora e il giorno:

     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 

    produzione:

     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 

    L’ho trovato da qualche altra parte. Mi piace questa risposta!

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