Calculate the number of records for each date between 2 dates

by

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

More Related Contents:

  1. Getting Dates between a range of dates
  2. I dont know sql syntax for my table [closed]
  3. How do I UPDATE from a SELECT in SQL Server?
  4. Get top 1 row of each group
  5. How to return only the Date from a SQL Server DateTime datatype
  6. How to calculate age (in years) based on Date of Birth and getDate()
  7. CROSS JOIN vs INNER JOIN in SQL
  8. Referring to a Column Alias in a WHERE Clause
  9. Function vs. Stored Procedure in SQL Server
  10. Generate a resultset of incrementing dates in TSQL
  11. converting Epoch timestamp to sql server(human readable format)
  12. How do I split a delimited string so I can access individual items?
  13. Common Table Expression, why semicolon?
  14. What’s the best way to select the minimum value from several columns?
  15. T-SQL split string based on delimiter
  16. Counting DISTINCT over multiple columns
  17. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  18. Why use Select Top 100 Percent?
  19. How to check if a stored procedure exists before creating it
  20. Correct use of transactions in SQL Server
  21. Can an INNER JOIN offer better performance than EXISTS
  22. Find the real column name of an alias used in a view?
  23. Stored procedure that exports data into csv files only exports to one file
  24. Get Start and End Date from Week Number SQL Server
  25. How to get the identity of an inserted row?
  26. How to create a pivot query in sql server without aggregate function
  27. TSQL – How to use GO inside of a BEGIN .. END block?
  28. SQL take just the numeric values from a varchar
  29. SQL Server FOR XML Path make repeating nodes
  30. Convert bigint to datetime

Leave a Comment