SQL Count to include zero values

Question

I have created the following stored procedure that is used to count the number of records per day between a specific range for a selected location:

[dbo].[getRecordsCount] 
@LOCATION as INT,
@BEGIN as datetime,
@END as datetime

SELECT
ISNULL(COUNT(*), 0) AS counted_leads, 
CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)), 3) as TIME_STAMP 
FROM HL_Logs
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp))

but the problem is that the result does not show the days where there are zero records, I pretty sure that it has something to do with my WHERE statement not allowing the zero values to be shown but I do not know how to over come this issue.

Thanks in advance Neil

Answer 1

Not so much the WHERE clause, but the GROUP BY. The query will only return data for rows that exist. That means when you're grouping by the date of the timestamp, only days for which there are rows will be returned. SQL Server can't know from context that you want to "fill in the blanks", and it wouldn't know what with.

The normal answer is a CTE that produces all the days you want to see, thus filling in the blanks. This one's a little tricky because it requires a recursive SQL statement, but it's a well-known trick:

WITH CTE_Dates AS
(
    SELECT @START AS cte_date
    UNION ALL
    SELECT DATEADD(DAY, 1, cte_date)
    FROM CTE_Dates
    WHERE DATEADD(DAY, 1, cte_date) <= @END
)
SELECT
cte_date as TIME_STAMP,
ISNULL(COUNT(HL_Logs.Time_Stamp), 0) AS counted_leads, 
FROM CTE_Dates
LEFT JOIN HL_Logs ON DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)) = cte_date
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY cte_date

Breaking it down, the CTE uses a union that references itself to recursively add one day at a time to the previous date and remember that date as part of the table. If you ran a simple statement that used the CTE and just selected * from it, you'd see a list of dates between start and end. Then, the statement joins this list of dates to the log table based on the log timestamp date, while preserving dates that have no log entries using the left join (takes all rows from the "left" side whether they have matching rows on the "right" side or not). Finally, we group by date and count instead and we should get the answer you want.