Solved

How to return a row with a zero count

Posted on 2008-10-16
9
524 Views
Last Modified: 2012-05-05
I am trying to return a result that shows for each month between a set of dates a count of associated records for each of the months.

The query I have written works fine except that it doesn't include any months with zero records. If the query was involving a join then I believe I could use a LEFT JOIN to ensure that all records are returned, however this query is just on 1 table.

Does anyone know how I can include rows with no records?

The kind of result I would want would be:

January 2008            6
February 2008          3
March 2008               0
April 2008                  2

With my current SQL the March row would not be returned because it has a zero count.

Many thanks,
SELECT DATENAME(Month,DateAdded) AS 'Month', Year(DateAdded) As 'Year', Count(id) As 'Total'
FROM Candidate
WHERE (dateAdded >= '10/01/2007') and (dateAdded <= '05/30/2008')
GROUP BY Year(DateAdded), month(DateAdded), DATENAME(Month,DateAdded)

Open in new window

0
Comment
Question by:robert_raw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22732916
The typical approach is to have a "calendar" table with all of the dates in it.  Your query then becomes:


SELECT DATENAME(Month, c.[Date]) AS 'Month', Year(c.[Date]) As 'Year', Count(d.id) As 'Total'
FROM Calendar c LEFT JOIN Candidate d ON c.[Date] = d.DateAdded
WHERE (c.[Date] >= '10/01/2007') and (c.[date] <= '05/30/2008')
GROUP BY DATENAME(Month, c.[Date]), Year(c.[Date])
0
 

Author Comment

by:robert_raw
ID: 22732981
Thanks for a quick reply. Would this approach require the calendar table to be managed to always ensure that the correct dates are stored in the table?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22733148
change count(id) to count(*) your were counting distinct ID's rather than records, however, for there to be a zero count, then, there cannot be any rows...

maybe you want to just count if between those dates ?

in which case:


SELECT DATENAME(Month,DateAdded) AS 'Month', Year(DateAdded) As 'Year', Count(case when (dateAdded >= '10/01/2007') and (dateAdded <= '05/30/2008')
 then 1 else 0 end) As 'Total'
FROM Candidate
GROUP BY Year(DateAdded), month(DateAdded), DATENAME(Month,DateAdded)

0
Industry Leaders: 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!

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22733516
robert_raw said:
>>Thanks for a quick reply. Would this approach require the calendar table to be managed to always ensure
>>that the correct dates are stored in the table?

That depends.  One approach would be to have the calendar table contain every date between, say, 1 Jan 1950
and 31 Dec 2100; for almost all purposes, that would probably be sufficient.  Another approach would be to build
it dynamically:

DECLARE @start datetime, @end datetime, @counter datetime
SET @start = '1 Oct 2007'
SET @end = '30 May 2008'
SET @counter = @start

CREATE TABLE #calendar ([Date] datetime)

WHILE @counter <= @end BEGIN
    INSERT INTO #calendar ([Date]) VALUES (@counter)
    SET @counter = DATEADD(d, 1, @counter)
END

SELECT DATENAME(Month, c.[Date]) AS 'Month', Year(c.[Date]) As 'Year', Count(d.id) As 'Total'
FROM #calendar c LEFT JOIN Candidate d ON c.[Date] = d.DateAdded
WHERE (c.[Date] >= '10/01/2007') and (c.[date] <= '05/30/2008')
GROUP BY DATENAME(Month, c.[Date]), Year(c.[Date])

DROP TABLE #calendar
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22734645
Or as a function:

which once built you can simply use it in a select statement such as

select datename(mm,period),year(period), counter as total
from dbo.uGet_Monthly_Candidates('01 Oct 2007','30 May 2008')
order by period

but create it first (a once-off exercise, once created can re-use as often as needed)...


create function uGet_Monthly_Candidates( @start datetime, @end datetime)
returns @tbl table (period datetime, counter int)
as
begin
 
declare @calendar table ([Date] datetime)
 
WHILE @start <= @end BEGIN
    INSERT INTO @calendar values (@start)
    SET @start = dateadd(mm,1,@start)
END
 
INSERT @tbl 
SELECT convert(varchar(6),c.[Date],112)+'01', Count(id)
FROM @calendar c LEFT JOIN Candidate d ON c.[Date] = convert(varchar(8),d.DateAdded,112)
GROUP BY convert(varchar(6),c.[Date],112)+'01'   -- just make it yyyymm01
 
return
END
GO

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22735401
I suggest a more generic approach, creating a table of sequential numbers, which will be used as a date offset to a specified starting date.  I'll post code in the next post to create the seq nums table.

The main code would then be something like this:

SELECT DATENAME(Month,date) AS 'Month', Year(date) As 'Year', Count(id) As 'Total'
FROM (
    SELECT DATEADD(MONTH, seqNum, CAST('10/01/2007' AS DATETIME)) AS date
    FROM seqNums
    WHERE DATEADD(MONTH, seqNum, CAST('10/01/2007' AS DATETIME)) BETWEEN '10/01/2007' AND '05/30/2008'
) AS dates
LEFT JOIN Candidate ON dateAdded >= date AND dateAdded < DATEADD(MONTH, 1, date)
ORDER BY [Year], [Month]
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22735403
DECLARE @maxValueToGenerate INT

-- chg next value to the highest value you need;
-- the current code supports up to 10M - 1, but you could increase that if you wanted to

SET @maxValueToGenerate = 16000

IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums

CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )

INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] +
      [1Ms] + [10Ms]
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
      SELECT [100s]
      FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
      ) inlineData
      WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
      SELECT [1000s]
      FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
      ) inlineData
      WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
      SELECT [10Ks]
      FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
      ) inlineData
      WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
      SELECT [100Ks]
      FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
      ) inlineData
      WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
      SELECT [1Ms]
      FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
      ) inlineData
      WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
      SELECT [10Ms]
      FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
      ) inlineData
      WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms]

DBCC SHOWCONTIG(seqNums) --make sure table is highly contig for max performance

SELECT COUNT(*) FROM seqNums --verify that the expected number of rows were generated
0
 

Author Closing Comment

by:robert_raw
ID: 31506816
Thanks matthewspatrick, the dynamic calendar table worked great. Many thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22739102
interesting, may I suggest you look at the date join in your selected answer. if it is a datetime in the candidate table, and there are time components, then you may want to address that. May I enquire if you looked at / tried my posting ? please do not get me wrong, it is just a matter of curiosity and interest.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question