Aaron Akin

January 21, 2009

Recursive Common Table Expressions – Part 2

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 7:48 pm

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.

Advertisements

2 Comments »

  1. 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 | Reply

  2. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: