Avatar of Howard Bash
Howard Bash
Flag for United States of America

asked on 

t-sql aggregate problem

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 UserCount

from log_hist T

Where
  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)

Open in new window

Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
ralmada

8/22/2022 - Mon