Iterative queries using Common table Expressions

Common Table Expression or CTEs was a very useful feature which got added from SQL 2005. Though they’re mostly associated to recursive query scenario, they can be used for scenarios involving iterative logic also. The purpose of this article is to show one such practical scenario solution using CTEs.
Consider the case of Order Fulfillment application where we’ve multiple batches of products and Order needs to be fulfilled based on one or more batches. The order of fulfillment should be on FIFO based ie earlier batches emptied first. See how we can apply CTE based solution in such a scenario.
For simplicity assume two tables one containing order quantity information and other table giving product batch information.The attempt is to allocate product against order based on the batch sequence. See below how CTE iteratively does the allocation and calculates the price. The logic is as follows

1. The first CTE sequences the product batches in the order of date and generates a sequence number
2. The anchor part of CTE captures first batch for each product
3. The recursively part by means of sequence number captures the next batch in sequence and calculates total allocated quantity so far. This is each time compared to the order quantity and stopped once it reaches (or overshoots) it. In case of overshooting, it does a partial allocation in last batch to make it equal.

DECLARE @Tbl1 table
(
ItemNumber int,
Quantity int
)
INSERT @Tbl1
SELECT 1234562,15 UNION ALL
SELECT 3423555,8

DECLARE @Tbl2 table
(

ItemNumber int,
Quantity int,
UnitPrice Decimal(5,2),
Date datetime
)
INSERT @Tbl2
SELECT 1234562,12,8.50,’2011-01-20′ UNION ALL
SELECT 1234562,1,10.50,’2011-02-24′ UNION ALL
SELECT 1234562 , 2 , 12.00 ,’2011-05-12′ UNION ALL
SELECT 1234562, 3 , 11.50 ,’2011-06-03′ UNION ALL
SELECT 1234562,4 , 11.80 ,’2011-08-29′ UNION ALL
SELECT 3423555, 6 , 15.50 ,’2011-03-13′ UNION ALL
SELECT 3423555, 12 , 21.50 ,’2011-06-03′ UNION ALL
SELECT 3423555, 1 , 28.50 ,’2011-11-23′

;With tbl2mod
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ItemNumber ORDER BY Date) AS Rn,*
FROM @Tbl2
)
,PriceCalc
AS
(
SELECT t1.ItemNumber,t1.Quantity,t2.UnitPrice,CASE WHEN t2.Quantity > t1.Quantity THEN t1.Quantity ELSE t2.Quantity END AS AllocQty,t2.Date,CASE WHEN t2.Quantity > t1.Quantity THEN t1.Quantity ELSE t2.Quantity END AS runQty,t2.Rn
FROM @Tbl1 t1
CROSS APPLY (SELECT TOP 1 UnitPrice,Quantity,Date ,Rn
FROM tbl2mod
WHERE ItemNumber = t1.ItemNumber
ORDER BY Date) t2

UNION ALL

SELECT p.ItemNumber,p.Quantity,q.UnitPrice,CASE WHEN p.runQty + q.Quantity > p.Quantity THEN p.Quantity – p.runQty ELSE q.Quantity END,q.Date,CASE WHEN p.runQty + q.Quantity > p.Quantity THEN p.Quantity ELSE p.runQty + q.Quantity END,q.Rn
FROM PriceCalc p
INNER JOIN tbl2mod q
ON q.ItemNumber = p.ItemNumber
AND q.Rn = p.Rn + 1
WHERE p.runQty + q.Quantity <= p.Quantity
OR (p.runQty < p.Quantity AND p.runQty + q.Quantity > p.Quantity)
)

SELECT ItemNumber,Date,AllocQty,UnitPrice,AllocQty * UnitPrice AS TotalPrice
FROM PriceCalc
ORDER BY ItemNumber,Date

Output

——————————————————————————————————-

ItemNumber Date AllocQty UnitPrice TotalPrice

——————————————————————————————————

1234562 2011-01-20 00:00:00.000 12 8.50 102.00

1234562 2011-02-24 00:00:00.000 1 10.50 10.50

1234562 2011-05-12 00:00:00.000 2 12.00 24.00

3423555 2011-03-13 00:00:00.000 6 15.50 93.00

3423555 2011-06-03 00:00:00.000 2 21.50 43.00