Generate a row in an aggregation query for a date with no results against it.

Question

I'm sorry if the title question isn't very clear but i don't think i can expalain my problem in a single sentance.

I have a table with a number of different types of events in it all recorded against a date.

I'm querying the table and grouping based on a subset of the date (month and year).

SELECT DATENAME(MONTH, event_date_time) + ' ' + DATENAME(YEAR, event_date_time), COUNT(reason)
FROM blacklist_history
WHERE (event_date_time BETWEEN DATEADD(mm,-6, '20/12/2009 23:59:59') AND '20/12/2009 23:59:59')
GROUP BY (DATENAME(MONTH, event_date_time) + ' ' + DATENAME(YEAR, event_date_time))
ORDER BY CONVERT(DATETIME, DATENAME(MONTH, event_date_time) + ' ' + DATENAME(YEAR, event_date_time)) ASC

(I normally have a variable for the date field)

This query returns the following against my data:

August    2009    15358
September 2009    48722
October   2009    19143
November  2009    4205
December  2009    3286

Now what i want to do is have the query also return for July 2009 but return a count of 0. I know that SQL Server can't just magic this out of thin air, but i can't quite see in my head how i would create the data i want to join against my result to fill the empty space for July.

Any suggestions would be helpful.(using SQL Server 2005)

Thanks

Answer 1

The usual approach for this is to just have a table of dates in your database and do LEFT JOINs from it to your data.

Answer 2

You could insert a list of all the months you're interested in, into a table variable and then do an OUTER join from that onto the table containing your data.

Answer 3

WITH    months (d) AS
        (
        SELECT  CAST('2009-01-01' AS DATETIME)
        UNION ALL
        SELECT  DATEADD(month, d, 1)
        FROM    months
        WHERE   d <= '2015-01-01'
        )
SELECT  d, COUNT(reason)
FROM    months
JOIN    blacklist_history bh
ON      event_date_time >= d
        AND event_date_time < DATEADD(month, d, 1)
GROUP BY
        d