Contando el número de filas agrupadas por date y hora

Estoy rastreando los datos de input de la tienda del cliente en Microsoft SQL Server 2008 R2 que se ve así:

DoorID DateTimeStamp EntryType 1 2013-09-02 09:01:16.000 IN 1 2013-09-02 09:04:09.000 IN 1 2013-09-02 10:19:29.000 IN 1 2013-09-02 10:19:30.000 IN 1 2013-09-02 10:19:32.000 OUT 1 2013-09-02 10:26:36.000 IN 1 2013-09-02 10:26:40.000 OUT 

No quiero contar las filas OUT , solo IN .

Creo que debe estar agrupado en Date e DoorID , luego obtenga las horas totales.

Me gustaría que salga así.

 Date DoorID HourOfDay TotalInPersons 2013-09-02 1 0 0 2013-09-02 1 1 0 2013-09-02 1 2 0 2013-09-02 1 3 0 2013-09-02 1 4 0 2013-09-02 1 5 0 2013-09-02 1 6 0 2013-09-02 1 7 0 2013-09-02 1 8 0 2013-09-02 1 9 2 2013-09-02 1 10 3 2013-09-02 1 11 0 2013-09-02 1 12 0 2013-09-02 1 13 0 2013-09-02 1 14 0 2013-09-02 1 15 0 2013-09-02 1 16 0 2013-09-02 1 17 0 2013-09-02 1 18 0 2013-09-02 1 19 0 2013-09-02 1 20 0 2013-09-02 1 21 0 2013-09-02 1 22 0 2013-09-02 1 23 0 

 SELECT [Date] = CONVERT(DATE, DateTimeStamp), DoorID, HourOfDay = DATEPART(HOUR, DateTimeStamp), TotalInPersons = COUNT(*) FROM dbo.tablename WHERE EntryType = 'IN' GROUP BY CONVERT(DATE, DateTimeStamp), DoorID, DATEPART(HOUR, DateTimeStamp) ORDER BY [Date], DoorID, HourOfDay; 

Por supuesto, si necesita todas las horas, incluso cuando no hay filas representadas, aquí hay una solución (que limita la salida para cualquier día solo a las puertas que tienen al less una input IN en ese día):

 ;WITH h AS ( SELECT TOP (24) h = number FROM Master..spt_values WHERE type = N'P' ORDER BY number ), doors AS ( SELECT DISTINCT DoorID, [Date] = CONVERT(DATE,DateTimeStamp) FROM dbo.tablename WHERE EntryType = 'IN' ) SELECT d.[Date], d.DoorID, HourOfDay = hh, TotalInPersons = COUNT(t.EntryType) FROM doors AS d CROSS JOIN h LEFT OUTER JOIN dbo.tablename AS t ON CONVERT(DATE, t.DateTimeStamp) = d.[Date] AND t.DoorID = d.DoorID AND DATEPART(HOUR, t.DateTimeStamp) = hh AND t.EntryType = 'IN' GROUP BY d.[Date], d.DoorID, hh ORDER BY d.[Date], d.DoorID, hh; 

Qué tal algo como esto:

 SELECT CAST(DateTimeStamp AS DATE) AS Date ,DoorID ,DATEPART(HOUR, DateTimeStamp) AS HourOfDay ,COUNT(*) AS TotalInPersons FROM StoreTable WHERE EntryType = 'IN' GROUP BY CAST(DateTimeStamp AS DATE) ,DoorID ,DATEPART(HOUR, DateTimeStamp) 

Esto debería funcionar. Adiviné cómo usaría DoorID y TotalPersons, pero la lógica general es correcta

 SELECT CONVERT(date,dateColumn) AS Date, datepart(hh,dateColumn) AS HourOfDay, DoorID, COUNT(people) AS TotalPersons FROM yourtable WHERE EntryType = 'IN' GROUP BY CONVERT(date,dateColumn), datepart(hh,dateColumn), DoorID ORDER BY CONVERT(date,dateColumn), datepart(hh,dateColumn)