We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hour show up but there are gaps and the query excludes these. We still want to show the hours that do not have activity and display a zero so that zero value can then be charted. The query we using looks like this …

select datepart(Year, dev_time) as Year,
datepart(Month, dev_time) as Month,
datepart(Day, dev_time) as Day,
datepart(Hour, dev_time) as Hour,
count(tdm_msg) as Total_ACTIVITES
where tdm_msg = ‘4162′ and dev_time >= DATEADD(day, - 1, GETDATE())
group by datepart(Year, dev_time) ,
datepart(Month, dev_time) ,
datepart(Day, dev_time),
datepart(Hour, dev_time)
order by datepart(Year, dev_time) asc,
datepart(Month, dev_time) asc,
datepart(Day, dev_time) asc,
datepart(Hour, dev_time) asc
8 Years
Discussion Span
Last Post by huangzhi

You cannot use just select command.
You must use cursor to list the time list.
Try and modify code below:

set nocount on
declare @CounterDate smalldatetime,
	@MaxDate smalldatetime

create table #tmpAct (
  ActDate smalldatetime,
  Act varchar(255)

create table #tmpChart (
  StartTime smalldatetime

insert #tmpAct (ActDate, Act)
  select '2008/12/22 08:15:00', 'Arrive'
  union all
  select '2008/12/22 12:00:00', 'Out'
  union all
  select '2008/12/22 13:00:00', 'In'
  union all
  select '2008/12/22 16:35:00', 'Go home'

select @CounterDate = '2008/12/22 00:00:00'
select @MaxDate = '2008/12/22 23:59:00'

while @CounterDate < @MaxDate
  insert #tmpChart (StartTime) select @CounterDate
  select @CounterDate = dateadd(hh, 1, @CounterDate)

select * 
  from	#tmpChart C left join #tmpAct A on
	A.ActDate >= C.StartTime and
	A.ActDate < dateadd(hh, 1, C.StartTime)

drop table #tmpChart
drop table #tmpAct
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.