Tuesday, January 19, 2010

Nested CTEs

Had my first need for nested CTEs today - for a table with prices by object and another with adjustment factors by object, I needed to multiply the prices by all factors occurring after the price date. This could be done in the C++ code I was working on via an ugly for loop for each price date, but I knew there had to be a better, set-based way within SQL.

The answer was nested Common Table Expressions (CTEs), which Microsoft officially says are not supported, but which can actually be implemented by defining multiple CTEs (with a comma between each) and using the first one in the second.

For this application, the first CTE orders the object ids and adjustment factors and adds row numbers. An ORDER BY would have sufficed to order everything, but in order to avoid inefficient subqueries in the next CTE, I needed consistent row numbering. The second CTE aggregates the adjustment factors for each date by recursing down the ordered factors for each object and multiplying them as it goes.

This solution condenses all the selection logic into one set of SQL statements, is compact, fairly readable, and has a better complexity profile than the for loop alternative.

(Table and column names changed to protect confidentiality.)

WITH AdjFactorsOrdered (ObjectId, AdjDate, AdjFactor, ord) AS
(SELECT ObjectId, AdjDate, AdjFactor, ROW_NUMBER() OVER (PARTITION BY ObjectId ORDER BY AdjDate desc)
FROM vwAdjFactors
WHERE AdjDate > '2009-01-01'
AND AdjDate <= '2010-01-01'
AND ISNULL(AdjFactor, 1) != 1
AND SubTypeId != 1
AdjFactorsCombined (ObjectId, AdjDate, AdjFactor, ord) AS
(SELECT ObjectId, AdjDate, AdjFactor, ord
FROM AdjFactorsOrdered
WHERE ord = 1
SELECT mul.ObjectId, mul.AdjDate, base.AdjFactor * mul.AdjFactor as AdjFactor, mul.ord
FROM AdjFactorsCombined base
INNER JOIN AdjFactorsOrdered mul
ON mul.ord = base.ord + 1
AND mul.ObjectId = base.ObjectId
SELECT ObjectId, AdjDate, AdjFactor
FROM AdjFactorsCombined
ORDER BY ObjectId, AdjDate

No comments: