Solved

How to return a row with a zero count

Posted on 2008-10-16
9
516 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 Query with Proper Case logic 2 34
backup and restore 21 30
AD and SQL Server 2016 2 29
install report service in sccm2012 3 19
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 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