October 14, 2009 at 8:13 am
Hi folks, I am hoping someone can help me. I am trying to have a query return an aggregate based on the hours in a day. BUT this only returns rows for hours that have data. I would like it to return a row even if the aggregate count is 0 during the hour.
Here is an example.
SELECT hour(report_date) as 'Hour', COUNT(report_date) as 'Total'
FROM reports
where report_date >= #1/1/2009#
and report_date <= #1/31/2009#
group by hour(report_date)
Hour....Total
------------
1............45
2............40
3............21
4............66
5..............2
6..............1
As you can see, this does not return hours 7 through 23 because there are no reports the correspond to those times. Is there a way to generate the hours in this sequence?
Thanks for the help!
~Dan
There are ll types of people: those who understand Roman Numerals, and those who do not.
October 14, 2009 at 8:20 am
You have to left outer join from your list of known values...
Try this (Untested but should be fine),
if not please post ddl and sample data.
SELECT values.number as 'Hour', COUNT(report_date) as 'Total'
FROM master..spt_values values
left outer join reports
on report_date >= #1/1/2009#
and report_date <= #1/31/2009#
and hour(report_date) = values.number
where TYPE='p' and number between 0 and 23
October 14, 2009 at 8:24 am
If you have a Numbers table, you can select from that, and join it to a count from your reports table.
select Number as Hour,
(select count(*)
from reports
where report_date >= '1/1/2009'
and report_date < '2/1/2009'
and datepart(hour, report_date) = Numbers.Number) as Total
from dbo.Numbers;
(I changed the Where clause slightly because the way you have it written, it won't get any reports from 31 Jan unless they are from exactly midnight, and I'm guessing that you want all of January.)
If you don't have a Numbers table, create one. They're incredibly useful. Search "numbers table" on this site, as well as "tally table" (Jeff Moden calls it that), you'll find articles on creating and using them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 3:42 pm
Hey that's great! I created a Numbers table to handle this! Thank you for the replies, both were very helpful.
😀
~Dan
There are ll types of people: those who understand Roman Numerals, and those who do not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply