Calculate the number of records for each date between 2 dates

I have to create a query to return results for a multi-axis chart. I need to count the number of Ids created for each date between 2 dates. I tried this:

 DECLARE @StartDate datetime2(7) = '11/1/2020',
         @EndDate datetime2(7) = '2/22/2021'

 ;WITH Date_Range_T(d_range) AS 
     (
       SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate) - @StartDate, 0) 
       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] on ([tbl_Support_Requests].CreatedDate Between @StartDate AND @EndDate) 
  GROUP BY d_range ORDER BY d_range ASC

Of course, the problem is with the ;WITH which returns the error

Operand type clash: datetime2 is incompatible with int.

The above works if I give it a specific number of days from the current date like:

 ;WITH Date_Range_T(d_range) AS 
 (
   SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 6, 0) 
   UNION ALL SELECT DATEADD(DAY, 1, d_range) 
   FROM Date_Range_T 
   WHERE DATEADD(DAY, 1, d_range) < GETDATE()
 )

Which returns:

enter image description here

The problem is that I cannot figure out how to substitute the date range.

2 answers

  • answered 2021-02-22 22:53 Dale K

    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.

  • answered 2021-02-23 16:17 Charlieface

    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
          FROM Nums
        )
    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