dban00b
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!!
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)
datepart(hh,Create_Time) as [Hour],
count(*) as [Count]
from SALES
group by datepart(yyyy,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!!
DECLARE @hour tinyint
SET @hour = 0
WHILE @hour < 24
BEGIN
END
SET @hour = 0
WHILE @hour < 24
BEGIN
END
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]
select datepart(mm,Create_Time) as [Month],
datepart(dd,Create_Time) as [Day],
datepart(yyyy,Create_Time)
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)
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
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
Sorry, had a malfunction.
Is the date a parameter in the procedure? Hopefully?
Is the date a parameter in the procedure? Hopefully?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
You're going to have to create the [Month], [Day], [Year], [Hour] in the table variable also.
Mike
Ha, you're too quick for me. Go it.
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.
You'll have to use an isnull(column,0) to return a zero for the rows that don't exist.