Insert Missing Hours for chart

[ffgordy]

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
from TCKT_ACT
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

[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
begin
  insert #tmpChart (StartTime) select @CounterDate
  select @CounterDate = dateadd(hh, 1, @CounterDate)
end

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