Return 0 for NULL values in a simple count and group by date

Question

I'm doing a simple count query for an SSRS chart, and while it looks like a common question, I've not really found an answer that suits my situation. This is my query:

SELECT TOP 30       CAST(qa.Created As Date) As 'Date',
                    COUNT(qa.Created) As 'Count'
FROM                QAs qa
GROUP BY            CAST(qa.Created As Date)
ORDER BY            'Date' DESC

This returns something like:

Date       | Count

2014-11-10 | 2

2014-11-08 | 3

2014-11-07 | 8

Which when put into a line chart, doesn't show the dip down to 0 on the 9th and is a bit confusing for my users. What I want to do is have all of the last 30 days appear in order, even if they are at 0. I've been told to do this with COALESCE() but I can't seem to get that working either. Where am I going wrong?

Answer 1

Use a Recursive CTE to generate dates for last 30 days.

;WITH cte
     AS (SELECT Cast(dateadd(day,-30,Getdate()) AS DATE) AS dates
         UNION ALL
         SELECT Dateadd(day, 1, dates)
         FROM   cte
         WHERE  dates < cast(Getdate() as date)
SELECT a.Dates           AS [Date],
       Count(qa.Created) AS [Count]
FROM   cte a
       LEFT JOIN QAs qa
              ON a.dates = Cast(qa.Created AS DATE)
GROUP  BY a.Dates 
ORDER  BY a.Dates 

Answer 2

I'd probably go with NoDisplayName's recursive CTE but for an alternative, if you happen to be stuck in something prior to 2005 which I was for a long time.

--Build a table of dates
DECLARE @dates AS TABLE([date] date)
DECLARE @i int

SET @i = 30

WHILE @i > 0
BEGIN
    INSERT INTO @dates([date])
    SELECT DATEADD(d, -1 * @i, GETDATE())
    SET @i = (@i - 1)
END


--Join into those dates so that no date is excluded
SELECT [date], SUM(dateCount)
FROM (
    SELECT d.[date], CASE WHEN qa.Created IS NULL THEN 0 ELSE 1 END AS dateCount
    FROM @dates d
        LEFT JOIN QAs qa ON CAST(qa.Created AS date) = d.[date]
) AS dateCounts
GROUP BY [date]