asked on

Getting Zero Count Records to show up in a Result Set

I'm trying to get a view/report/query out of SQL that reports my websites sales per hour per day.
For example,
4-1-2006 00:00~00:59 = 5 sales
4-1-2006 01:00~01:59 = 2 sales
4-1-2006 02:00~02:59 = 0 sales
4-1-2006 03:00~03:59 = 10 sales

and so on...

Now my query works just fine, but for groups that have no records, SQL is smart enough NOT to give me Zero Count records in my result set. The Problem is I WANT those zero count records.

Here's my test query:

select datepart(mm,Create_Time) as [Month],
      datepart(dd,Create_Time) as [Day],
      datepart(yyyy,Create_Time) as [Year],
      datepart(hh,Create_Time) as [Hour],
      count(*) as [Count]
from SALES
group by datepart(yyyy,Create_Time), datepart(mm,Create_Time), datepart(dd,Create_Time), datepart(hh,Create_Time)

and here's the records it spits out for April 1st:

Month Day Year Hour Count
4      1      2006      0      3
4      1      2006      3      2
4      1      2006      8      1
4      1      2006      12      2
4      1      2006      14      1
4      1      2006      15      1
4      1      2006      16      1
4      1      2006      17      1
4      1      2006      18      1
4      1      2006      20      2
4      1      2006      21      1
4      1      2006      22      4
4      1      2006      23      2

I only get 13 records instead of 24 records. It goes from the 0 hour (midnight) to the 3 hour (3:00 am) skipping 1am and 2am because no sales were generated during that time.

Is there a way to force verbose records or even a work-around to create the empty records so I can get to this:

Month Day Year Hour Count
4      1      2006      0      3
4      1      2006      1      0
4      1      2006      2      0
4      1      2006      3      2
4      1      2006      4      0
4      1      2006      5      0
4      1      2006      6      0
4      1      2006      7      0
4      1      2006      8      1
4      1      2006      9      0
4      1      2006      10      0
4      1      2006      11      0
4      1      2006      12      2
4      1      2006      13      0
4      1      2006      14      1
4      1      2006      15      1
4      1      2006      16      1
4      1      2006      17      1
4      1      2006      18      1
4      1      2006      19      0
4      1      2006      20      2
4      1      2006      21      1
4      1      2006      22      4
4      1      2006      23      2

It doesn't have to be done in one query, subqueries, querying a view, etc will be just fine. I'll even be happy with a stored procedure :)

Thanks a lot!!

Atlanta_Mike

I think the best way is to populate a temp table or table variable with a "seeded" value for every hour and create a left join to the "seeded" table.

You'll have to use an isnull(column,0) to return a zero for the rows that don't exist.

Atlanta_Mike

DECLARE @hour tinyint
SET @hour = 0

WHILE @hour < 24
BEGIN

END

DonKronos

create a table say TheHours with the hours 0 to 23. then change your select like this.

select datepart(mm,Create_Time) as [Month],
datepart(dd,Create_Time) as [Day],
datepart(yyyy,Create_Time) as [Year],
TheHours.[Hour],
count(*) as [Count]
from TheHours left outer join SALES on TheHours.[Hour] = datepart(hh,Create_Time) as [Hour]
group by datepart(yyyy,Create_Time), datepart(mm,Create_Time), datepart(dd,Create_Time), TheHours.[Hour]

Brian Crowe

you need to add an outer join to a source with all possible values

DECLARE @Hours TABLE (
Hour tinyint
)

DECLARE @i int
SET @i = 0
WHILE @i < 24
BEGIN
INSERT INTO @Hours (Hour) VALUES (@i)
SET @i = @i + 1
END

SELECT [Month], [Day], [Year], [Hour], ISNULL([Count], 0) AS [Count]
FROM (
SELECT MONTH(Create_Time) as [Month],
DAY(Create_Time) as [Day],
YEAR(Create_Time) as [Year],
DATEPART(hour, Create_Time) as [Hour],
COUNT(*) as [Count]
FROM SALES
GROUP BY YEAR(Create_Time), MONTH(Create_Time), DAY(Create_Time), DATEPART(hour,Create_Time)
) AS SalesSummary
RIGHT OUTER JOIN @Hours AS Hours
ON SalesSummary.[Hour] = Hours.Hour

Atlanta_Mike

Sorry, had a malfunction.

Is the date a parameter in the procedure? Hopefully?

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Atlanta_Mike

Bri, that's almost right. You have the issue with the NULL [Month], [Day], [Year], [Hour].

You're going to have to create the [Month], [Day], [Year], [Hour] in the table variable also.

Mike

Atlanta_Mike

Ha, you're too quick for me. Go it.

dban00b

ASKER

Thanks BriCrowe!! It's a little complex but it works. I just tried is for the entire month of April and got exactly what i want.