sql server - Include monthly counts including months where data doesn't exist

Question

This seems like it should be easy, but I cannot seem to figure it out. I have a table that has an Artifact name and Modification_Date among other things. I have a query that is getting counts of document modifications by month. There are a couple of months where there were no modifications. Since I am querying and grouping on the Modification_Date there is no result returned for those months with no modifications. Ideally, in the result set I'd like the month there and 0 for the Quantity.

SELECT CONVERT(NVARCHAR(7), Modification_Date, 120) [Month], 
    COUNT(Artifact) as Quantity
    FROM table
WHERE Modification_Date > DATEADD(month, -6, getdate())
GROUP BY CONVERT(NVARCHAR(7), Modification_Date, 120)
ORDER BY [Month] DESC

This gets me a result similar to:

Month       Quantity
-------     --------
2013-02     10
2012-11     12
2012-10     5
2012-09     29

As you can see, the months of December 2012 and January 2013 are not in the result set. I would like to have those months represented there with a 0 Quantity so that I can use that data in a SQL Report bar graph and have those months represented with a 0 value. Currently on the bar graph it skips those months completely. Is there a way to generate the yyyy-mm Month column for the last 6 months rather than just using the Modification_Date?

Answer 1

Don't convert dates to strings to strip time or day, use date arithmetic. Converting to strings is less efficient and also requires a scan of the entire table.

If you're going to convert to a fixed length string, don't use NVARCHAR, use CHAR. What Unicode characters are you going to need to support in a numeric date? Umlauts? Pound signs? Hieroglyphics?

Here is an example that uses a catalog view to generate 6 rows, then subtracts months away from the current date to group by the previous 6 months (and an index on Modification_Date should be used, unlike your current approach). This is not entirely intuitive the first time you see it, but you can see my series on generating sets without loops (part 1 | part 2 | part 3).

;WITH x(m) AS 
(
  SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
  FROM sys.all_objects
  ORDER BY [object_id]
)
SELECT [Month] = x.m, Quantity = COALESCE(COUNT(t.Artifact), 0)
FROM x
LEFT OUTER JOIN dbo.tablename AS t
ON t.Modification_Date >= x.m
AND t.Modification_Date < DATEADD(MONTH, 1, x.m)
GROUP BY x.m
ORDER BY x.m DESC;

Note that this will not include the current month. If you want to shift to include October -> March instead of September -> February, just change this line:

+ 1 - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 

And if formatting as YYYY-MM is absolutely essential, you can do this:

;WITH y(m) AS 
(
  SELECT TOP 6 DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
    - (ROW_NUMBER() OVER (ORDER BY [object_id])), 0) 
  FROM sys.all_objects
  ORDER BY [object_id]
),
x([Month], Quantity)
AS
(
  SELECT [Month] = y.m, Quantity = COALESCE(COUNT(t.Artifact), 0)
  FROM y
  LEFT OUTER JOIN dbo.tablename AS t
  ON t.Modification_Date >= y.m
  AND t.Modification_Date < DATEADD(MONTH, 1, y.m)
  GROUP BY y.m
)
SELECT [Month] = CONVERT(CHAR(7), [Month], 120), Quantity
FROM x
ORDER BY [Month] DESC;