asked on

How to return a row with a zero count

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

Patrick Matthews

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])

robert_raw

ASKER

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?

Mark Wills

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)

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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

Scott Pletcher

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]

Scott Pletcher

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

robert_raw

ASKER

Thanks matthewspatrick, the dynamic calendar table worked great. Many thanks.

Mark Wills

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.