How to Use TSQL to Group Dates by Range

In this article, you’ll learn how to use TSQL to group dates by range. This is a fairly common task that involves checking if a date falls into a certain range based on another date. For example, if someone gives you their birthday and you want to know if it falls in the range of October 1st – November 30th or something similar. In this article, we will look at various ways to do so as well as some best practices for doing so efficiently. Keep reading to get started…

Create a CTE

A common SQL construct that you can use to group dates is a Common Table Expression or CTE. A CTE is similar to a VIEW but it is not persisted to the database. It is a temporary construct that exists only within the current query. The syntax for creating a CTE is a slight variation of the syntax used to create a VIEW. Instead of CREATE VIEW, we specify the CTE name and the columns that will be part of the CTE. There are no rules against having a column name within a CTE that is also a column name in other tables.

Define the Date Ranges in the CTE

Next, we need to define the date ranges that we want to group together. We do this as a query within the CTE that we add to our base query. We use the DATEADD function to determine the number of days between two dates. Using an INNR JOIN we connect our date ranges to our core select.

WITH ranges AS (
    SELECT
        range_id,
        ISNULL(DATEADD(day,range_start,CAST(GETDATE() AS date)),Convert(DateTime, '1901-01-01', 101)) range_start,
        ISNULL(DATEADD(day,range_end  ,CAST(GETDATE() AS date)),Convert(DateTime, '2901-12-31', 101)) range_end,
              range_name

    FROM (VALUES
        (1,  -7,  0, '1 Week'),
        (2, -14, -8, '2 Weeks'),
        (3, -30,-15, '3 Weeks'),
        (4, -60,-31, '1 Month'),
        (5,NULL,-61, '2+ Months')
    ) r(range_id,range_start,range_end, range_name)
)
SELECT 
    transaction_no,
    customer_id,
    operator_id,
    purchase_date,
   range_name
FROM sales 
INNER JOIN  ranges ON purchase_date BETWEEN range_start AND range_end
GROUP BY   range_id

The last step is to use the GROUP BY clause to group the dates by the ranges that we have created. You can see that the dates are now grouped by the ranges that we specified in the CTE.

Conclusion

In this article, you’ve learned how to use TSQL to group dates by range. A common SQL construct that you can use to group dates is a Common Table Expression or CTE. You can use the DATEADD function to determine the number of days between two dates. You can use the CTE and add the number of date ranges that you want to group by. This can be particularly useful if you have dates that fall on different days but you want to group them together for your solution.