Comment un CTE récursif peut-il distribuer et suivre les quantités de consommables sur des lots regroupés, en fournissant une ventilation détaillée des quantités restantes et en attente ?

Suivi de l'inventaire des consommables sur plusieurs lots

Une gestion efficace des stocks nécessite un suivi précis des quantités de consommables sur les lots regroupés. Cela nécessite une requête qui déduit avec précision la consommation de plusieurs lots en fonction de critères spécifiques. La solution présentée utilise une expression de table commune (CTE) récursive pour y parvenir.

Approche CTE récursive :

Le cœur de la solution est un CTE récursif, intelligemment nommé « Amos », qui itère sur des lots regroupés. Cela commence par initialiser chaque pool avec son premier lot. Le CTE traite ensuite de manière récursive les lots suivants, mettant à jour dynamiquement la quantité consommée.

Pour chaque lot, le CTE calcule la RunningQuantity (quantité restante) et la RemainingDemand (quantité restante) en fonction de la consommation cumulée. Ces valeurs sont ensuite utilisées pour éclairer les calculs pour les lots suivants au sein du même pool.

Détails de sortie :

L'ensemble de résultats finaux fournit une ventilation complète pour chaque pool et lot, notamment :

• Piscine : L'identifiant de la piscine.
• Lot : Le numéro de lot au sein de la piscine.
• Quantité : La quantité initiale du lot.
• QuantitéConsumée : La quantité consommée de ce lot.
• RunningQuantity: La quantité restante après consommation.
• RemainingDemand : La quantité restant à consommer.
• SurplusOrDeficit : Indique tout excédent ou déficit après traitement du dernier lot d'un pool.

Exemple de mise en œuvre :

L'exemple suivant illustre la fonctionnalité de la requête à l'aide d'exemples de données :

-- 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;

Cette explication raffinée et cet exemple permettent une compréhension plus claire de la fonctionnalité récursive du CTE et de son application dans la gestion des stocks. Le SurplusOrDeficit calcul est désormais explicitement lié au dernier lot de chaque pool.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!