SQL Server: How to select all days in a date range even if no data exists for some days

Question

I have an app that needs to show a bar graph for activity over the last 30 days. The graph needs to show all days even if there is no activity for the day.

for example:

DATE       COUNT ================== 1/1/2011   5  1/2/2011   3  1/3/2011   0 1/4/2011   4 1/5/2011   0 etc.... 

I could do post processing after the query to figure out what dates are missing and add them but was wondering if there is an easier way to do it in SQL Server. Thanks much

Answer 1

You can use a recursive CTE to build your list of 30 days, then join that to your data

--test select cast('05 jan 2011' as datetime) as DT, 1 as val into #t union all select CAST('05 jan 2011' as datetime), 1  union all select CAST('29 jan 2011' as datetime), 1   declare @start datetime = '01 jan 2011' declare @end   datetime = dateadd(day, 29, @start)  ;with amonth(day) as (     select @start as day         union all     select day + 1         from amonth         where day < @end ) select amonth.day, count(val)     from amonth      left join #t on #t.DT = amonth.day group by amonth.day   >>  2011-01-04 00:00:00.000 0 2011-01-05 00:00:00.000 2 2011-01-06 00:00:00.000 0 2011-01-07 00:00:00.000 0 2011-01-08 00:00:00.000 0 2011-01-09 00:00:00.000 0 ... 

Answer 2

Using CTE:

WITH DateTable AS (     SELECT CAST('20110101' AS Date) AS [DATE]     UNION ALL     SELECT DATEADD(dd, 1, [DATE])     FROM DateTable     WHERE DATEADD(dd, 1, [DATE]) < cast('20110201' as Date) ) SELECT dt.[DATE], ISNULL(md.[COUNT], 0) as [COUNT] FROM [DateTable] dt LEFT JOIN [MyData] md ON md.[DATE] = dt.[DATE] 

This is assuming everything's a Date; if it's DateTime, you'll have to truncate (with DATEADD(dd, 0, DATEDIFF(dd, 0, [DATE]))).