Aaron Akin

March 18, 2009

Nested Common Table Expressions

Filed under: CTE,SQL,SQL 2005,SQL 2008,TSQL — Aaron Akin @ 8:01 pm

If you’ve looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can’t define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner.

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.


January 20, 2009

Recursive Common Table Expressions – Part 1

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 6:43 pm


I recently had a project that required me to find the number of employees who were scheduled for every 15 minute increment, for every business day for a week, and for many stores.  Yikes!  Sure, I could use tons of loops and temp tables to do this, but I’m all about set based queries whenever possible…and I knew this was possible and I love a challenge!

It finally occurred to me that I could use the self-referencing (recursive) capabilities of Common Table Expressions (introduced in SQL 2005) to the mimic loops I needed to accomplish my goal.

I took some of the recursive CTE queries that I came up with for my project and turned them into 4 examples.  It was quite a bit of info, so I’ll be spreading them across 2 posts.


Create a free website or blog at WordPress.com.