group by day with missing days

Question

I have a sql server table with the following structure and data:

Created              ,  keyword
'2017-10-03 19:18:00',  'test7'
'2017-10-07 01:06:00',  'test3'
'2017-10-07 15:19:00',  'test2'
'2017-10-07 21:39:00',  'test10'
'2017-10-08 00:36:00',  'test3'
'2017-10-08 01:26:00',  'test13'
'2017-10-08 01:33:00',  'test9'
'2017-10-08 08:23:00',  'test13'
'2017-10-08 09:35:00',  'test9'
'2017-10-08 12:38:00',  'test9'
'2017-10-08 15:07:00',  'test2'
'2017-10-10 05:09:00',  'test4'

I would like to run a query that counts activity and group it by day, also accounting for days when nothing was recorded and show the activity for those days as zero. As such I want a query that will return the result set below:

'2017-10-03', 1
'2017-10-04', 0
'2017-10-05', 0
'2017-10-06', 0
'2017-10-07', 3
'2017-10-08', 7
'2017-10-09', 0
'2017-10-03', 1

I know how to run a query and group it by count for days, but not how to account for days nothing was recorded. As I am new to Sql, I would really appreciate it if someone can provide a working example. Thanks in advance

Answer 1

Try this:

declare @startDate date = '2017-10-01'
declare @endDate date = '2017-10-31'

;with cte as (
select cast(@startDate as date) [dayOfYear]
union all
select DATEADD(day, 1, [dayOfYear]) from cte
where [dayOfYear] < @endDate
)

select dayOfYear, SUM(case when Created is null then 0 else 1 end) from cte
left join MY_TABLE [T] on cte.dayOfYear = CAST(T.Created as date)
group by dayOfYear

The logic is as follows:

get table with all days between @startDate and @endDate (the CTE - I specified first and last of October). Then we left join your table and when the days has no match, we define corresponding value to 0, 1 otherwise. Then it's enough to sum these values day-wise.