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!!
LVL 1
dban00bAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
looking at it again you're going to have to have a table containing all of the Year/Month/day/hour values you want to include otherwise you'll get null for any non-hour fields

DECLARE @TimeIncrement TABLE (
   [Year] int,
   [Month] tinyint,
   [Day] tinyint,
   Hour tinyint
)

DECLARE @start datetime,
   @end datetime
SET @start = '20060401'
SET @end = '20060402'

WHILE @start < @end
BEGIN
   INSERT INTO @TimeIncrement ([Year], [Month], [Day], Hour)
   VALUES (YEAR(@start), MONTH(@start), DAY(@start), DATEPART(hour, @start))
   SET @start = DATEADD(hour, 1, @start)
END

SELECT TI.[Month], TI.[Day], TI.[Year], TI.[Hour], ISNULL(SalesSummary.[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 @TimeIncrement AS TI
   ON SalesSummary.[Year] = TI.[Year]
   AND SalesSummary.[Month] = TI.[Month]
   AND SalesSummary.[Day] = TI.[Day]
   AND SalesSummary.Hour = TI.Hour
0
 
Atlanta_MikeCommented:
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.
0
 
Atlanta_MikeCommented:
DECLARE @hour tinyint
SET @hour = 0

WHILE @hour < 24
BEGIN




END
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
DonKronosCommented:
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]

0
 
Brian CroweDatabase AdministratorCommented:
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


0
 
Atlanta_MikeCommented:
Sorry, had a malfunction.

Is the date a parameter in the procedure? Hopefully?
0
 
Atlanta_MikeCommented:
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
0
 
Atlanta_MikeCommented:
Ha, you're too quick for me. Go it.
0
 
dban00bAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.