How to Reduce Pooled Lot Quantity Using SQL Recursive CTE?

Using SQL Recursive CTE to Decrement Pooled Lot Quantities

This SQL Server 2005 solution employs a recursive Common Table Expression (CTE) to iteratively reduce pooled lot quantities based on consumption data. The process efficiently handles scenarios where consumption exceeds the available quantity across multiple lots within a pool.

Here's the SQL code:

-- Sample data setup
DECLARE @Pooled_Lots AS TABLE (
  Id INT,
  Pool INT,
  Lot INT,
  Quantity INT
);

INSERT INTO @Pooled_Lots (Id, Pool, Lot, Quantity) VALUES
(1, 1, 1, 5),
(2, 1, 2, 10),
(3, 1, 3, 4),
(4, 2, 1, 7),
(5, 3, 1, 1),
(6, 3, 2, 5);

DECLARE @Pool_Consumption AS TABLE (
  Id INT,
  PoolId INT,
  QuantityConsumed INT
);

INSERT INTO @Pool_Consumption (Id, PoolId, QuantityConsumed) VALUES
(1, 1, 17),
(2, 2, 8),
(3, 3, 10);


-- Recursive CTE to calculate remaining quantities
WITH LotQuantities AS (
    SELECT 
        PL.Pool, 
        PL.Lot, 
        PL.Quantity, 
        PC.QuantityConsumed,
        PL.Quantity as RunningQuantity,
        PC.QuantityConsumed as RemainingDemand,
        CASE WHEN PL.Quantity >= PC.QuantityConsumed THEN 0 ELSE PC.QuantityConsumed - PL.Quantity END as SurplusOrDeficit
    FROM @Pooled_Lots PL
    LEFT JOIN @Pool_Consumption PC ON PL.Pool = PC.PoolId
    WHERE PL.Lot = 1 -- Start with the first lot in each pool

    UNION ALL

    SELECT 
        lq.Pool, 
        PL.Lot, 
        PL.Quantity, 
        lq.QuantityConsumed,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN lq.RunningQuantity + PL.Quantity ELSE lq.QuantityConsumed END,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN lq.RemainingDemand - PL.Quantity ELSE 0 END,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN 0 ELSE lq.RemainingDemand - (lq.RunningQuantity + PL.Quantity) END
    FROM LotQuantities lq
    JOIN @Pooled_Lots PL ON lq.Pool = PL.Pool AND PL.Lot = lq.Lot + 1
    WHERE lq.RemainingDemand > 0
)
SELECT * FROM LotQuantities;

The resulting dataset shows the remaining quantity for each lot after the consumption deduction. The CTE recursively processes lots within each pool until the consumed quantity is fully accounted for. Note that negative values in SurplusOrDeficit indicate a shortfall.

The above is the detailed content of How to Reduce Pooled Lot Quantity Using SQL Recursive CTE?. For more information, please follow other related articles on the PHP Chinese website!