Need Help on Query

  • 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.

  • 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



    Clear Sky SQL
    My Blog[/url]

  • 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

  • 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.

  • Hi,

    Thank you for explaining I was looking for it.

Viewing 5 posts - 1 through 4 (of 4 total)

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