Get “zero” for a count at dates without records

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

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.

Advertisement

Answer