In my last post, Recursive Common Table Expressions – Part 1, I gave a brief overview of common table expressions (CTE) and gave some examples on how to use them to write recursive queries. The first two examples showed how to find the missing numbers, or dates, in a sequence. It was assumed that you already had a table with a list of numbers or dates in it. The examples in this post will expand on the first two examples to show you how to build a range of numbers or dates given a starting and ending point.
Example 3 – Create a sequential range of numbers
In this example, I’ll show you how to use a CTE to create a range of sequential numbers given a minimum (lower limit) and maximum (upper limit) number. First, I’m going to declare the variables that I’ll be using. The @LowerLimit and @UpperLimit variables will be used as starting and ending points of the range of numbers. The @IncludeLowerLimit and @IncludeUpperLimit variables will tell the query if it needs to return the @LowerLimit and @UpperLimit values in the result set. In this case, I want to use the range from 15 to 20, and I do want the limits to be a part of the result set.
DECLARE
@LowerLimit INT -- Lower limit
, @UpperLimit INT -- Upper limit
, @IncludeLowerLimit BIT -- Includes lower limit in results
, @IncludeUpperLimit BIT -- Includes upper limit in results
SELECT
@LowerLimit = 15
, @UpperLimit = 20
, @IncludeLowerLimit = 1
, @IncludeUpperLimit = 1
;WITH CTE_Range (Number) AS
(
SELECT @LowerLimit + 1
UNION ALL
SELECT Number + 1
FROM CTE_Range
WHERE Number < @UpperLimit + 1
)
SELECT Number - @IncludeLowerLimit AS Number
FROM CTE_Range
WHERE Number < (@UpperLimit + @IncludeLowerLimit + @IncludeUpperLimit)
ORDER BY Number
This query will return a sequential range of numbers from 15 through 20.
Number
-----------
15
16
17
18
19
20
(6 row(s) affected)
You can also change the @IncludeLowerLimit and @IncludeUpperLimit variables to shift the ranges a little. I’ll now change the values for both of these variables to 0 and rerun the query.
SELECT
@IncludeLowerLimit = 0
, @IncludeUpperLimit = 0
This query will now only return a sequential range of numbers from 16 through 19.
Number
-----------
16
17
18
19
(4 row(s) affected)
Example 4 – Create a sequential range of dates
This example takes the same concept from example 3 and applies it to datetime values instead of numbers. First, I’m going to declare the variables that I’ll be using. The @LowerLimit and @UpperLimit variables will be used as starting and ending points of the range of datetime values. The @IncludeLowerLimit and @IncludeUpperLimit variables will tell the query if it needs to return the @LowerLimit and @UpperLimit values in the result set.
The one variable that I added to this example is the @RangeInterval variable. This allows you to specify the number of years, months, days, hours, minutes, seconds, etc. between the two datetime limits that should be returned in the result set. If you want to change the datepart value (i.e. MONTH, DAY, HOUR, etc.), you will have to change that manually since the datepart value cannot be assigned from a variable. However, you could add some more custom code to get this to work from a variable.
In this case, I’m going to use the range from 1/15/2009 12:00 AM to 1/20/2009 12:00 AM, use 1 day as the interval, and I do want the limits to be a part of the result set.
DECLARE
@LowerLimit DATETIME -- Lower limit
, @UpperLimit DATETIME -- Upper limit
, @RangeInterval INT -- Interval between dates/times
, @IncludeLowerLimit BIT -- Includes lower limit in results
, @IncludeUpperLimit BIT -- Includes upper limit in results
SELECT
@LowerLimit = '1/15/2009 12:00 AM'
, @UpperLimit = '1/20/2009 12:00 AM'
, @RangeInterval = 1
, @IncludeLowerLimit = 1
, @IncludeUpperLimit = 1
;WITH CTE_Range (Date) AS
(
SELECT DATEADD(DAY,@RangeInterval,@LowerLimit)
UNION ALL
SELECT DATEADD(DAY,@RangeInterval,Date)
FROM CTE_Range
WHERE Date < DATEADD(DAY,@RangeInterval,@UpperLimit)
)
SELECT DATEADD(DAY,-@RangeInterval*@IncludeLowerLimit,Date) AS Date
FROM CTE_Range
WHERE Date < DATEADD(DAY,@RangeInterval*@IncludeLowerLimit,
DATEADD(DAY,@RangeInterval*@IncludeUpperLimit,@UpperLimit))
ORDER BY Date
This query will return a sequential range of datetime values every 1 day from 1/15/2009 12:00 AM to 1/20/2009 12:00 AM.
Date
-----------------------
2009-01-15 00:00:00.000
2009-01-16 00:00:00.000
2009-01-17 00:00:00.000
2009-01-18 00:00:00.000
2009-01-19 00:00:00.000
2009-01-20 00:00:00.000
(6 row(s) affected)
Now I’ll change the @RangeInterval, @LowerLimit and @UpperLimit variables to show you some different output. I’ll also need to change the datepart in every DATEADD function from DAY to HOUR so that it will return a datetime value for every 6 hours between 1/15/2009 4:00 AM and 1/16/2009 4:00 AM.
SELECT
@LowerLimit = '1/15/2009 4:00 AM'
, @UpperLimit = '1/16/2009 4:00 AM'
, @RangeInterval = 4
, @IncludeLowerLimit = 1
, @IncludeUpperLimit = 1
;WITH CTE_Range (Date) AS
(
SELECT DATEADD(HOUR,@RangeInterval,@LowerLimit)
UNION ALL
SELECT DATEADD(HOUR,@RangeInterval,Date)
FROM CTE_Range
WHERE Date < DATEADD(HOUR,@RangeInterval,@UpperLimit)
)
SELECT DATEADD(HOUR,-@RangeInterval*@IncludeLowerLimit,Date) AS Date
FROM CTE_Range
WHERE Date < DATEADD(HOUR,@RangeInterval*@IncludeLowerLimit,
DATEADD(HOUR,@RangeInterval*@IncludeUpperLimit,@UpperLimit))
ORDER BY Date
This query will now only return a sequential range of datetime values every 6 hours from 1/15/2009 4:00 AM to 1/20/2009 4:00 AM.
Date
-----------------------
2009-01-15 04:00:00.000
2009-01-15 10:00:00.000
2009-01-15 16:00:00.000
2009-01-15 22:00:00.000
2009-01-16 04:00:00.000
(5 row(s) affected)
Conclusion
You might wonder why you would ever need these queries, and I would have thought the same thing…until recently. I hope this helps someone out there.
Part 1 of my posts on Recursive Common Table Expressions. Includes examples for finding missing numbers and dates in a sequence.
Pingback by Recursive Common Table Expressions – Part 1 « Aaron Akin — January 21, 2009 @ 7:48 pm |
I forgot to mention that by default, common table expressions have a max recursion of 100 and if you’re dealing with larger data sets, you might get the following error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
To overcome this, add OPTION (MAXRECURSION 0) to the end of the entire CTE statement.
Thanks to EdG for pointing this out.
Comment by Aaron Akin — February 24, 2009 @ 7:25 am |