sql - Counting number of rows grouped by date and hour -

i tracking customer store entry data in microsoft sql server 2008 r2 looks this:

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 

i don't want count out rows, in.

i believe needs grouped on date, , doorid, hours totals.

i come out this.

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(*) dbo.tablename entrytype = 'in' group    convert(date, datetimestamp),   doorid,   datepart(hour, datetimestamp) order   [date], doorid, hourofday; 

of course if need hours, no rows represented, here 1 solution (which limits output day doors have @ least 1 in entry on day):

;with h  (   select top (24) h = number master..spt_values    type = n'p' order number ), doors (   select distinct doorid, [date] = convert(date,datetimestamp)    dbo.tablename entrytype = 'in' ) select    d.[date],   d.doorid,   hourofday = h.h,   totalinpersons = count(t.entrytype)  doors d cross join h left outer join dbo.tablename t on convert(date, t.datetimestamp) = d.[date] , t.doorid = d.doorid , datepart(hour, t.datetimestamp) = h.h , t.entrytype = 'in' group d.[date], d.doorid, h.h order d.[date], d.doorid, h.h;