How can a recursive CTE distribute and track consumable quantities across pooled lots, providing a detailed breakdown of remaining and outstanding quantities?

Tracking Consumable Inventory Across Multiple Lots

Efficient inventory management requires precise tracking of consumable quantities across pooled lots. This necessitates a query that accurately deducts consumption from multiple lots based on specific criteria. The solution presented utilizes a recursive Common Table Expression (CTE) to achieve this.

Recursive CTE Approach:

The core of the solution is a recursive CTE, cleverly named "Amos," which iterates through pooled lots. It begins by initializing each pool with its first lot. The CTE then recursively processes subsequent lots, dynamically updating the consumed quantity.

For each lot, the CTE calculates the RunningQuantity (remaining quantity) and RemainingDemand (outstanding quantity) based on the cumulative consumption. These values are then used to inform the calculations for subsequent lots within the same pool.

Output Details:

The final result set provides a comprehensive breakdown for each pool and lot, including:

• Pool: The ID of the pool.
• Lot: The lot number within the pool.
• Quantity: The initial quantity of the lot.
• QuantityConsumed: The quantity consumed from this lot.
• RunningQuantity: The remaining quantity after consumption.
• RemainingDemand: The outstanding quantity yet to be consumed.
• SurplusOrDeficit: Indicates any surplus or deficit after processing the last lot in a pool.

Example Implementation:

The following example demonstrates the query's functionality using sample data:

-- Sample Data (Pooled Lots)
DECLARE @Pooled_Lots 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);

-- Sample Data (Pool Consumption)
DECLARE @Pool_Consumption TABLE (Id INT, Pool INT, QuantityConsumed INT);
INSERT INTO @Pool_Consumption (Id, Pool, QuantityConsumed) VALUES
(1, 1, 17), (2, 2, 8), (3, 3, 10);


-- Recursive CTE Query
WITH Amos AS (
    -- Anchor Member: Initialize with the first lot of each pool
    SELECT
        PL.Pool,
        PL.Lot,
        PL.Quantity,
        PC.QuantityConsumed,
        CASE
            WHEN PC.QuantityConsumed IS NULL THEN PL.Quantity
            WHEN PL.Quantity >= PC.QuantityConsumed THEN PL.Quantity - PC.QuantityConsumed
            ELSE 0
        END AS RunningQuantity,
        CASE
            WHEN PC.QuantityConsumed IS NULL THEN 0
            WHEN PL.Quantity >= PC.QuantityConsumed THEN 0
            ELSE PC.QuantityConsumed - PL.Quantity
        END AS RemainingDemand
    FROM
        @Pooled_Lots PL
    LEFT JOIN
        @Pool_Consumption PC ON PC.Pool = PL.Pool
    WHERE
        Lot = 1
    UNION ALL
    -- Recursive Member: Process subsequent lots
    SELECT
        PL.Pool,
        PL.Lot,
        PL.Quantity,
        CTE.QuantityConsumed,
        CASE
            WHEN CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand THEN CTE.RunningQuantity + PL.Quantity - CTE.RemainingDemand
            ELSE 0
        END,
        CASE
            WHEN CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand THEN 0
            ELSE CTE.RemainingDemand - CTE.RunningQuantity - PL.Quantity
        END
    FROM
        Amos CTE
    JOIN
        @Pooled_Lots PL ON PL.Pool = CTE.Pool AND PL.Lot = CTE.Lot + 1
)
-- Final Result Set
SELECT
    *,
    CASE
        WHEN Lot = (SELECT MAX(Lot) FROM @Pooled_Lots WHERE Pool = Amos.Pool) THEN RunningQuantity - RemainingDemand
        ELSE NULL
    END AS SurplusOrDeficit
FROM
    Amos
ORDER BY
    Pool, Lot;

This refined explanation and example provide a clearer understanding of the recursive CTE's functionality and its application in inventory management. The SurplusOrDeficit calculation is now explicitly tied to the last lot in each pool.

The above is the detailed content of How can a recursive CTE distribute and track consumable quantities across pooled lots, providing a detailed breakdown of remaining and outstanding quantities?. For more information, please follow other related articles on the PHP Chinese website!