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

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

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

Solution 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]))).

Solution 3:

@Alex K.'s answer is completely correct, but it doesn't work for versions that do not support Recursive common table expressions (like the version I'm working with). In this case the following would do the job.

DECLARE @StartDate datetime = '2015-01-01'
DECLARE @EndDate datetime = SYSDATETIME()

;WITH days AS
(
  SELECT DATEADD(DAY, n, DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), 0)) as d
    FROM ( SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
           FROM sys.all_objects ORDER BY [object_id] ) AS n
)
select days.d, count(t.val)
    FROM days LEFT OUTER JOIN yourTable as t
    ON t.dateColumn >= days.d AND t.dateColumn < DATEADD(DAY, 1, days.d)
GROUP BY days.d
ORDER BY days.d;

Solution 4:

My scenario was a bit more complex than the OP example, so thought I'd share to help others who have similar issues. I needed to group sales orders by date taken, whereas the orders are stored with datetime.

So in the "days" lookup table I could not really store as a date time with the time being '00:00:00.000' and get any matches. Therefore I stored as a string and I tried to join on the converted value directly.

That did not return any zero rows, and the solution was to do a sub-query returning the date already converted to a string.

Sample code as follows:

declare @startDate datetime = convert(datetime,'09/02/2016')
declare @curDate datetime = @startDate
declare @endDate datetime = convert(datetime,'09/09/2016')
declare @dtFormat int = 102;
DECLARE @null_Date varchar(24) = '1970-01-01 00:00:00.000'

/* Initialize #days table */
select CONVERT(VARCHAR(24),@curDate, @dtFormat) as [Period] into #days

/* Populate dates into #days table */
while (@curDate < @endDate )
begin
    set @curDate = dateadd(d, 1, @curDate)
    insert into #days values (CONVERT(VARCHAR(24),@curDate, @dtFormat))
end

/* Outer aggregation query to group by order numbers */
select [Period], count(c)-case when sum(c)=0 then 1 else 0 end as [Orders],
sum(c) as [Lines] from
(
    /* Inner aggregation query to sum by order lines */ 
    select
        [Period], sol.t_orno, count(*)-1 as c   
        from (
            /* Inner query against source table with date converted */
            select convert(varchar(24),t_dldt, @dtFormat) as [shipdt], t_orno
                from salesorderlines where t_dldt > @startDate
        ) sol
        right join #days on shipdt = #days.[Period]     
        group by [Period], sol.t_orno
) as t
group by Period
order by Period desc

drop table #days

Sample Results:

Period      Orders  Lines
2016.09.09  388     422
2016.09.08  169     229
2016.09.07  1       1
2016.09.06  0       0
2016.09.05  0       0
2016.09.04  165     241
2016.09.03  0       0
2016.09.02  0       0