blog moving to 42sight.blogspot.com

moving the blog to 42sight.blogspot.com - where we continue the Data warehousing related discussions and our upcoming book
http://42sight.blogspot.com/

Experimenting with Recursive Common Table Expressions (CTE)

Having read Johnathon Parker's excellent post on how Recursive CTEs work in SQL Server
http://jonathanparker.com.au/archive/2007/04/16/recursive-common-table-expressions-ctes-in-sql-server-2005.aspx

We decided to try it out on something more complicated

The challenge we gave oursleves was to take the "SalesQuota" table which is in quartlery buckets and split it into monthly Buckets.

This can be acheived with 3 queries in Union as follows:

SELECT SalesPersonID, QuotaDate, SalesQuota / 3 AS SalesQuota

FROM Sales.SalesPersonQuotaHistory

UNION ALL


SELECT SalesPersonID, DATEADD(m, 1, QuotaDate) AS QuotaDate, SalesQuota / 3 AS SalesQuota
FROM Sales.SalesPersonQuotaHistory AS SalesPersonQuotaHistory_1


UNION ALL


SELECT SalesPersonID, DATEADD(m, 2, QuotaDate) AS QuotaDate, SalesQuota / 3 AS SalesQuota
FROM Sales.SalesPersonQuotaHistory AS SalesPersonQuotaHistory_2

An alternative Solution using Recursive CTE is as follows:

WITH SPQ AS (SELECT SalesPersonID, QuotaDate, SalesQuota / 3 AS SalesQuota , 1 as c1

FROM Sales.SalesPersonQuotaHistory


UNION ALL


SELECT SalesPersonID, DATEADD(m, 1 , QuotaDate) AS QuotaDate, SalesQuota, c1+1 as c1
FROM SPQ
WHERE c1<3)

SELECT SalesPersonID, QuotaDate, SalesQuota
from SPQ
ORDER BY SalesPersonID, QuotaDate

This repeats the second part twice in a recursive manner - providing the same results as the 1st triple union query.