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:
@LOCATION as INT,
@BEGIN as datetime,
@END as datetime
ISNULL(COUNT(*), 0) AS counted_leads,
CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)), 3) as TIME_STAMP
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp))
Not so much the WHERE clause, but the GROUP BY. Simply put, 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(*), 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.