Insert Missing Hours for chart

Expand Post »

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 …

sql Syntax (Toggle Plain Text)
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
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

Last edited by peter_budo; Dec 17th, 2008 at 8:14 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

ffgordy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Dec 2008

Permalink

Dec 22nd, 2008

Re: Insert Missing Hours for chart

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

MS SQL Syntax (Toggle Plain Text)
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
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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Hello brothers!!! your expert advice is needed!! show me the light!!!

Next Thread in MS SQL Forum Timeline: Need help on Replication

DaniWeb Message

Cancel Changes

User Name
Password