SQL Count to include null values

Question

I created the following stored procedure, which 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 as a result, days are not displayed when there are null entries, I'm sure this has something to do with my WHERE statement, not allowing to show null values, but I don’t know how to do this.

Thanks in advance Neil

Answer 1

Not so much a WHERE clause as a GROUP BY clause. Simply put, a query will only return data for existing rows. This means that when you group by timestamp date, only days for which there are rows will be returned. SQL Server cannot know from the context that you want to "fill in the blanks", and it would not know what s.

The usual answer is a CTE, which produces all the days you want to see, filling in the blanks. This is a bit complicated because it requires a recursive SQL statement, but this is a 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 

Interrupting it, the CTE uses a join that references recursively adds one day at a time before the previous date and remembers that date as part of the table. If you run a simple statement that used CTE and just selected *, you will see a list of dates between the beginning and the end. The operator then attaches this list of dates to the log table based on the date of the timestamp of the log, saving dates that have no log entries using the left join (takes all rows from the "left" side, regardless of whether they have the corresponding rows in the row "to the right) " or not). Finally, we group by date and score instead, and we should get the answer you want.

Answer 2

When there is no data to count, there is no row to return.

If you want to include empty days as 0, you need to create a table (or temporary table or subquery) to store the days and leave a connection with your request.

for example: something like

 SELECT COUNT(*) AS counted_leads, CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)), 3) as TIME_STAMP FROM TableOfDays left join HL_Logs on TableOfDays.Date = convert(date,HL_Logs.Time_Stamp) and ID_Location = @LOCATION WHERE TableOfDays.Date between @BEGIN and @END GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)) 

Answer 3

Use the left outer join. For instance,

 select count(stuff_ID), extra_NAME from dbo.EXTRAS left outer join dbo.STUFF on suff_EXTRA = extra_STUFF group by extra_NAME 

Answer 4

I recently had a similar task, and I used it as the basis for my work. However, as robwilliams explained, I, too, could not get this KeithS solution to work. The mine’s task was somewhat different, I did it for hours against the days, but I think that the solution of the question of neillism would be

 DECLARE @Start as DATETIME ,@End as DATETIME ,@LOCATION AS INT; WITH CTE_Dates AS ( SELECT @Start AS cte_date, 0 as 'counted_leads' UNION ALL SELECT DATEADD(DAY, 1, cte_date) as cte_date, 0 AS 'counted_leads' FROM CTE_Dates WHERE DATEADD(DAY, 1, cte_date) <= @End ) SELECT cte_date AS 'TIME_STAMP' ,COUNT(HL.ID_Location) AS 'counted_leads' FROM CTE_Dates LEFT JOIN HL_Logs AS HL ON CAST(HL.Time_Stamp as date) = CAST(cte_date as date) AND DATEPART(day, HL.Time_Stamp) = DATEPART(day,cte_date) AND HL.ID_Location = @LOCATION group by cte_date OPTION (MAXRECURSION 0)