I have a table with a date and a unique user id. I would like to generate a resultset that gives me the count of user table inserts per hour. Here is desired output:
DayOfWeek | Date | HourInterval | UserCount
Monday | 03/05/2009 | 1 | 12
Monday | 03/05/2009 | 2 | 32
Monday | 03/05/2009 | 3 | 19
Monday | 03/05/2009 | 4 | 72
Monday | 03/05/2009 | 5 | 4
Monday | 03/05/2009 | 6 | 11
...
select datename(dw,T.MY_DATETIME) as DayOfTheWeek, T.MY_DATETIME, datepart(hh,T.ACTLOG_DATETIME) as HourInterval count(T.USER_IDENTIFIER) as UserCountfrom log_hist TWhere T.MY_DATETIME>convert(datetime,'10/22/2009') AND T.MY_DATETIME<convert(datetime, '11/30/2009')group by T.MY_DATETIME, datename(dw,T.MY_DATETIME), datepart(hh,T.ACTLOG_DATETIME)