Calculate the number of records for each date between 2 dates

Solution 1:

Improving on Dale K's answer, I suggest you use a tally table or function, as this is usually more performant.

I have used Itzik Ben-Gan's well-known one below:

DECLARE @StartDate date = '2020-11-01', @EndDate date = '2021-02-22';

  WITH
    L0 AS ( SELECT 1 AS c 
            FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                        (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
    L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
    L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
    Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
              FROM L2 )
    Date_Range_T (d_range) AS (
      SELECT TOP(DATEDIFF(day, @StartDate, @EndDate) + 1)
          DATEADD(day, rownum - 1, @StartDate) AS d_range,
          DATEADD(day, rownum, @StartDate) AS d_rangeNext
      FROM Nums
    )
SELECT d_range, COUNT(Id) AS Total 
FROM Date_Range_T 
LEFT JOIN tbl_Support_Requests R
    ON R.CreatedDate >= T.d_range AND R.CreatedDate < T.d_rangeNext
GROUP BY d_range
ORDER BY d_range ASC

Solution 2:

No need to reinvent the wheel - there are many examples of recursive CTE calendar tables out there, similar to below.

DECLARE @StartDate date = '01-Nov-2020', @EndDate date = '22-Feb-2021';

WITH Date_Range_T (d_range) AS (
    SELECT @StartDate AS d_range
    UNION ALL
    SELECT DATEADD(DAY, 1, d_range) 
    FROM Date_Range_T 
    WHERE DATEADD(DAY, 1, d_range) < @EndDate
)
SELECT d_range, COUNT(Id) AS Total 
FROM Date_Range_T 
LEFT JOIN tbl_Support_Requests R ON R.CreatedDate = d_range
GROUP BY d_range
ORDER BY d_range ASC
-- Set to the max number of days you require
OPTION (MAXRECURSION 366);

Comments:

• Why use a datetime2 for a date?
• Do you definitely want < the end date or <=?
• Are you familiar with how between works - its not always intuitive.
• Alias all tables for better readability.
• Semi-colon terminate all statements.
• Consistent casing makes the query easier to read.
• Use an unambiguous date format for date strings.