Need help to understand SQL query

Question

I have the following code that gets the months between two date ranges using a CTE

declare
    @date_start DateTime,
    @date_end DateTime

;WITH totalMonths AS 
(
    SELECT 
        DATEDIFF(MONTH, @date_start, @date_end) totalM
),
numbers AS 
(
    SELECT 1 num

    UNION ALL

    SELECT n.num + 1 num 
    FROM numbers n, totalMonths c
    WHERE n.num <= c.totalM
)
SELECT 
    CONVERT(varchar(6), DATEADD(MONTH, numbers.num - 1, @date_start), 112)  
FROM 
    numbers 
OPTION (MAXRECURSION 0);

This works, but I do not understand how it works

Especially this part

numbers AS 
(
    SELECT 1 num

    UNION ALL

    SELECT n.num + 1 num 
    FROM numbers n, totalMonths c
    WHERE n.num <= c.totalM
)

Thanks in advance, sorry for my English

Lookup recursive common table expressions... Here's possibly a duplicate post: http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — sgeddes, Mar 07 '16 at 23:30
I wouldn't use a recursive CTE to "... get the months between two date ranges": massive overkill. Use a Numbers/tally table instead... — Mitch Wheat, Mar 07 '16 at 23:38

score 1 · Accepted Answer · answered Mar 07 '16 at 23:43

This query is using two CTEs, one recursive, to generate a list of values from nothing (SQL isn't really good at doing this).

totalMonths AS (SELECT DATEDIFF(MONTH, @date_start, @date_end) totalM),

This is part is basically a convoluted way of binding the result of the DATEDIFF to the name totalM. This could've been implemented as just a variable if you can declare things:

DECLARE @totalM int = DATEDIFF(MONTH, @date_start, @date_end);

Then you would of course use @totalM to refer to the value.

numbers AS (
    SELECT 1 num
    UNION ALL
    SELECT n.num+1 num FROM numbers n, totalMonths c
    WHERE n.num<= c.totalM
)

This part is essentially a simple loop implemented using recursion to generate the numbers from 1 to totalMonths. The first SELECT specifies the first value (1) and the one after that specifies the next value, which is int greater than the previous one. Evaluating recursive CTEs has somewhat special semantics so it's a good idea to read up on them. Finally the WHERE specifies the stopping condition so that the recursion doesn't go on forever.

What all this does is generate an equivalent to a physical "numbers" table that just has one column the numbers from 1 onwards.

The SELECT at the very end uses the result of the numbers CTE to generate a bunch of dates.

Note that the OPTION (MAXRECURSION 0) at the end is also relevant to the recursive CTE. This disables the server-wide recursion depth limit so that the number generating query doesn't stop short if the range is very long, or a bothersome DBA set a very low default limit.

shawnt00 · Answer 2 · 2016-03-08T01:00:27.847

totalMonths query evaluates to a scalar result (single value) indicating the number of months that need to be generated. It probably makes more sense to just do this inline instead of using a named CTE.

numbers generates a sequence of rows with a column called num starting at 1 and ending at totalM + 1 which was computed in the previous step. It is able to reference this value by means of a cross join. Since there's only one row it essentially just appends that one column to the table horizontally. The query is recursive so each pass adds a new row to the result by adding 1 to the last added row (really just the one column) until the value of the previously added row exceeds totalM. The first half of the union is the starting value; the second half refers to itself via from numbers and incrementally builds the result in a sort of loop.

The output is derived from the numbers input. One is subtracted from each num giving a range from 0 to totalM and that value is treated as the number of months to add to the starting date. The date value is converted to a varchar of length six which means the final two characters containing the day are truncated.

Suppose that @date_start is January 31, 2016 and @date_end is March 1, 2016. There is never any comparison of the actual date values so it doesn't matter that March 31 is generated in the sequence but also falls later than the passed @date_end value. Any dates in the respective start and end months can be chosen to generate identical sequences.

score 0 · Answer 3 · answered Mar 07 '16 at 23:33

SELECT 1 num

is your starting point of your recursive CTE and that is (numbers n) in the first teration.In the second iteration the out put of the first

SELECT n.num+1 num FROM numbers n, totalMonths c
WHERE n.num <= c.totalM

becomes numbers (n) and so on.

Need help to understand SQL query

3 Answers3