tsql - 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
...