sql - Get "zero" for a count at dates without records

Question

So I'm counting activity records from users in my system. I get the activity counter for each day in a certain month and year, just like the query that follows

SELECT CONVERT(date, VIS_DATETIME) AS DATETIME, COUNT(*) AS ACTIVITY
FROM ACTIVITY
WHERE DATEPART(year, VIS_DATETIME) = 2016 AND
      DATEPART(month, VIS_DATETIME) = 3
GROUP BY CONVERT(date, VIS_DATETIME)
ORDER BY CONVERT(date, VIS_DATETIME)

The question is, if, let's say, March 28th doesn't have any activity, it won't be even listed. But, for my charts API, I need to get that listed and with a 0 for the counter.

Obviously, accepting suggestions!

Answer 1

Create a table that contains all dates. Then do a left join with the Activity table. Group on the date, and do a COUNT on Activity.id. The left join ensures that all dates from the date table are included in the result set, even if they are not matched in the join clause.