MS SQL

MS SQL RSS

Insert Missing Hours for chart

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 1

Reputation: ffgordy is an unknown quantity at this point

Solved Threads: 0

ffgordy

Offline

Newbie Poster

Insert Missing Hours for chart

Dec 16th, 2008

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 …

 Help with Code Tags 
 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.

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: Insert Missing Hours for chart

Dec 22nd, 2008

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

 Help with Code Tags 
 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