I recently wanted to list a count of occurrences across a data range so that I could render a line graph. I knew that some of the dates would contain 0 occurrences, so I knew I'd need to list all dates within the range, and left join to the occurrence-by-date count. With the help of this SO post ( http://stackoverflow.com/a/23291758/3264286 ) I created the following SQL that I think will be pretty useful for similar requirements in the future:

DECLARE @MinDate DATE = (SELECT MIN([[date]]) FROM [[table]]),
@MaxDate DATE = (SELECT MAX([[date]]) FROM [[table]]);

WITH Dates AS (SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM sys.all_objects a CROSS JOIN sys.all_objects b)

SELECT d.[Date], ISNULL(p.[Count], 0) AS NumGenerated
FROM Dates d LEFT JOIN (SELECT CONVERT(DATE, [[date]]) GeneratedDate, Count(1) AS [Count]
FROM [[table]]
GROUP BY CONVERT(DATE, [[date]])) AS p ON d.[Date] = p.GeneratedDate
ORDER BY 1;