• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

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!!
0
dban00b
Asked:
dban00b
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Brian CroweCommented:
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
 
Brian CroweCommented:
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:
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now