Count of items per date, with no gaps in the dates

Question

I have a table of posts to a website, where each entry has its creation date.

I want to return a table like:

Date         Posts
16-11-2009   5
17-11-2009   0
18-11-2009   4
etc

But I'm struggling to find the right SQL. I can easily get a count of posts grouped by date, but this doesn't return the dates when there are no posts (eg it misses out 17-11-2009).

Is there some SQL that will give me what I want? Or should I use some non-SQL code to deal with the days with no posts?

Answer 1

WITH    dates AS
        (
        SELECT  CAST('2009-01-01' AS DATETIME) AS d
        UNION ALL
        SELECT  DATEADD(day, 1, d)
        FROM    dates
        WHERE   d <= GETDATE()
        )
SELECT  d, COUNT(posts.id)
FROM    dates
LEFT JOIN
        posts
ON      posts.date >= d
        AND posts.date < DATEADD(day, 1, d)
GROUP BY
        d
OPTION (MAXRECURSION 0)

Answer 2

A simple trick is to have a table with all the dates you need in it ... and then use an outer join between this table and the one with posts

If I remember it right joe celko advise something like that for holidays calculations and al.