SQL Server 2022 - GENERATE_SERIES

T-SQL 2022 has introduced a new feature called GENERATE_SERIES that allows users to generate a series of values between two specified values. This feature provides a simple and efficient way to generate sequences of numbers or dates that can be used in various scenarios such as reporting, data analysis, and data modeling.

Prior to the introduction of the GENERATE_SERIES function, T-SQL developers had to rely on various workarounds to generate sequences of numbers or dates. These workarounds included using recursive CTEs, temporary tables, or even custom functions. However, these methods were often inefficient, difficult to read and maintain, and had limitations on the number of values that could be generated.

With the introduction of the GENERATE_SERIES function, T-SQL developers can now easily generate sequences of numbers or dates with just a single line of code. The syntax for the function is simple and intuitive:

GENERATE_SERIES (start_value, end_value [, step_value])

The start_value and end_value parameters define the range of values to be generated, while the optional step_value parameter defines the increment between each value. If the step_value parameter is not specified, the default value of 1 is used.

Here's an example of how to use the GENERATE_SERIES function to generate a sequence of numbers:

SELECT * FROM GENERATE_SERIES(1, 10)

Unfortunately, unlike some other DBMS like PostgreSQL, the GENERATE_SERIES function in T-SQL does not support direct generation of date series. However, this can be achieved by converting a series of numbers to dates using the DATEADD function.

SELECT DATEADD(day, value, '2022-01-01') AS Date
FROM GENERATE_SERIES(0, DATEDIFF(day, '2022-01-01', '2022-01-10'))

Tess this and many other cases on SQLize.online

As a practical application of this function, I'll give the following example. Suppose we have a table of orders, Orders (for simplicity, we'll limit ourselves to two columns: Date and Amount), add some test data, and calculate the daily revenue:

CREATE TABLE Orders (
    Date date,
    Amount int
);

INSERT INTO Orders VALUES
('2023-01-02', 500), ('2023-01-02', 400), ('2023-01-05', 400);

SELECT Date, SUM(Amount) AS DailyAmount
FROM Orders
GROUP BY Date;

As you can see, the days with no sales are simply missing from the result:

+============+=============+
| Date | DailyAmount |
+============+=============+
| 2023-01-02 | 900 |
| 2023-01-05 | 400 |
+------------+-------------+

If we need to get the results for all dates in the first ten days of January, GENERATE_SERIES can help us do that:

WITH Decade AS (
    SELECT DATEADD(day, value-1, '2023-01-01') AS Date
    FROM GENERATE_SERIES(
        1, 
        DATEDIFF(day, '2023-01-01', '2023-01-10') + 1
    )
) SELECT 
    Decade.Date, COALESCE(SUM(Amount), 0) AS DailyAmount
FROM Decade
LEFT JOIN Orders ON Decade.Date = Orders.Date
GROUP BY Decade.Date;

Here we use a CTE to generate the date range, LEFT JOIN with the Orders table, and summing by date. For elegance, we use the COALESCE function to display zeros instead of NULL.
All of this is available online at SQLize.online.