Solved

How to return a row with a zero count

Posted on 2008-10-16
9
486 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:ScottPletcher
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:ScottPletcher
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now