Return missing hours from a table

Question

Post reply

Return missing hours from a table

Fred Gordy

Newbie

Points: 3
More actions
December 18, 2008 at 5:31 am

#199014

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_Incoming_Vehicles
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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post some sample data please Fred? The link in my sig will explain how to do this.

Many thanks

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Burrows SSC Guru Points: 65144 More actions · Answer 2

You need to match your data against a dataset of 24 hours, something like this

SELECT DATEPART(Year,a.dev_time) as [Year],

DATEPART(Month,a.dev_time) as [Month],

DATEPART(Day,a.dev_time) as [Day],

n.Number AS [Hour],

COUNT(a.tdm_msg) AS [Total_Incoming_Vehicles]

FROM Numbers n

LEFT JOIN TCKT_ACT a ON a.tdm_msg = '4162'

AND a.dev_time >= DATEADD(day, -1, GETDATE())

AND DATEPART(Hour,a.dev_time) = n.Number

WHERE n.Number BETWEEN 0 AND 23

GROUP BY DATEPART(Year,a.dev_time),DATEPART(Month,a.dev_time),DATEPART(Day,a.dev_time),n.Number

ORDER BY DATEPART(Year,a.dev_time),DATEPART(Month,a.dev_time),DATEPART(Day,a.dev_time),n.Number

Depending on the table structure and indexes, performance may not be great. Some fine tuning will be required.

Far away is close at hand in the images of elsewhere.
Anon.