How to Count Daily Records Within a Specified Date Range in SQL?

Calculate the Number of Records for Each Date Between 2 Dates

In database systems, it's often necessary to retrieve data based on a specified date range. SQL provides built-in functions and techniques to facilitate this task. This article addresses the challenge of calculating the number of records for each date within a given date range.

Problem Statement

Given two dates, the intention is to create a query that returns the count of records for each date within that range. To illustrate, let's assume we want to retrieve the count of support requests created between November 1, 2020, and February 22, 2021, from a table named tbl_Support_Requests.

Failed Attempt

An initial attempt to solve this problem involved using a Common Table Expression (CTE) called Date_Range_T to generate a sequential list of dates within the specified range. However, the CTE's recursive definition resulted in an operand type clash between datetime2 and int.

Solution

To overcome this issue, a more efficient approach is to utilize a tally table or function. Tally tables, also known as number generators, provide a series of sequential numbers that can serve as a source for generating a range of dates.

The following is an optimized solution using Itzik Ben-Gan's tally table function:

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

This solution generates a range of dates within the specified range (#StartDate# to #EndDate#), including the endpoint dates. It then joins this range table with the tbl_Support_Requests table on the CreatedDate column, filtering for records that fall within each date range. Finally, it groups the results by date and counts the number of records for each date.

The above is the detailed content of How to Count Daily Records Within a Specified Date Range in SQL?. For more information, please follow other related articles on the PHP Chinese website!