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

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 c开发者_StackOverflow社区an'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

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.

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.

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