Comment calculer le total courant sur une date spécifique ou mieux ?

Question

Je voudrais calculer les commandes qui peuvent être complétées et les dates manquantes ( diff ) après avoir effectué autant de commandes que possible pour le moment. Choisis dans l'ordre de FEFO .

En réfléchissant à ce problème, je pense qu'une sorte de somme courante basée à la fois sur les dates du stock et des commandes serait une solution. Sur la base de Calculer le total de la course / le solde de la course et d'autres fils de discussion similaires, il semble que cela corresponde bien au problème, mais je suis ouvert à d'autres solutions.

Exemple de code

DECLARE @stockTable TABLE (
    BATCH_NUM nvarchar(16),
    QUANTITY int, 
    DATE_OUTGO DATE
)

DECLARE @orderTable TABLE (
    ORDER_ID int,
    QUANTITY int, 
    DATE_OUTGO DATE
)

INSERT INTO @stockTable (BATCH_NUM, QUANTITY, DATE_OUTGO)
VALUES 
('1000', 10, '2017-08-25'),
('1001', 20, '2017-08-26'),
('1002', 10, '2017-08-27')

INSERT INTO @orderTable (ORDER_ID, QUANTITY, DATE_OUTGO)
VALUES
(1, 10, '2017-08-25'),
(1, 12, '2017-08-25'),
(2, 10, '2017-08-26'),
(3, 10, '2017-08-26'),
(4, 16, '2017-08-26')

SELECT 
    DATE_OUTGO,
    SUM(RunningTotal) AS DIFF
FROM (
    SELECT  
        orderTable.DATE_OUTGO AS DATE_OUTGO,
        RunningTotal = SUM(stockTable.QUANTITY - orderTable.QUANTITY ) OVER 
                       (ORDER BY stockTable.DATE_OUTGO ROWS UNBOUNDED PRECEDING)
    FROM 
        @orderTable orderTable
        INNER JOIN @stockTable stockTable 
           ON stockTable.DATE_OUTGO >= orderTable.DATE_OUTGO 
    GROUP BY 
        orderTable.DATE_OUTGO, 
        stockTable.DATE_OUTGO, 
        stockTable.QUANTITY, 
        orderTable.QUANTITY
    ) A
GROUP BY DATE_OUTGO

Résultats

The correct result would look like this.
-------------------------
| OT_DATE_OUTGO | DIFF  |
-------------------------
| 2017-08-25    | 0     |
-------------------------
| 2017-08-26    | -18   |
-------------------------

My result currently looks like this.
-------------------------
| OT_DATE_OUTGO | DIFF  |
-------------------------
| 2017-08-25    | 80    |
-------------------------
| 2017-08-26    | 106   |
-------------------------

J'ai supprimé les complexités comme les numéros d'articles, les différentes demandes simultanées (en utilisant la date exacte seulement et la date ou mieux) etc. pour simplifier le problème de base autant que possible.

Edit 1 :

Lignes mises à jour dans les deux tableaux et résultats (correctes et avec la requête originale). La première réponse donnait une différence de -12 le 2017-08-25 au lieu de 0. Mais le 2017-08-26 était correct.

Answer 1

Vous pouvez utiliser la requête suivante :

;WITH ORDER_RUN AS (
   SELECT SUM(SUM(QUANTITY)) OVER (ORDER BY DATE_OUTGO)  AS ORDER_RUNTOTAL,
          DATE_OUTGO
   FROM @orderTable
   GROUP BY DATE_OUTGO
), STOCK_RUN AS (
   SELECT SUM(SUM(QUANTITY)) OVER (ORDER BY DATE_OUTGO) AS STOCK_RUNTOTAL, 
          DATE_OUTGO
   FROM @stockTable
   GROUP BY DATE_OUTGO
)
SELECT ORR.DATE_OUTGO AS OT_DATE_OUTGO,
       X.STOCK_RUNTOTAL - ORDER_RUNTOTAL  AS DIFF
FROM ORDER_RUN AS ORR
OUTER APPLY (
   SELECT TOP 1 STOCK_RUNTOTAL
   FROM STOCK_RUN AS SR
   WHERE SR.DATE_OUTGO <= ORR.DATE_OUTGO
   ORDER BY SR.DATE_OUTGO DESC) AS X

Le premier CTE calcule le total courant de la commande, tandis que la seconde CTE calcule le total courant du stock. La requête utilise OUTER APPLY pour faire fonctionner le stock total jusqu'à la date la commande en cours a été effectuée.

Editar:

Si vous voulez consommer le stock de dates qui viennent dans le futur par rapport à la date de la commande, il suffit de remplacer :

WHERE SR.DATE_OUTGO <= ORR.DATE_OUTGO

avec

WHERE STOCK_RUNTOTAL <= ORDER_RUNTOTAL

dans le OUTER APPLY fonctionnement.

Edit 2 :

La requête améliorée suivante devrait, enfin, résoudre le problème :

;WITH ORDER_RUN AS (
   SELECT SUM(SUM(QUANTITY)) OVER (ORDER BY DATE_OUTGO)  AS ORDER_RUNTOTAL,
          DATE_OUTGO
   FROM @orderTable
   GROUP BY DATE_OUTGO
), STOCK_RUN AS (
   SELECT SUM(SUM(QUANTITY)) OVER (ORDER BY DATE_OUTGO) AS STOCK_RUNTOTAL, 
          SUM(SUM(QUANTITY)) OVER () AS TOTAL_STOCK,
          DATE_OUTGO
   FROM @stockTable
   GROUP BY DATE_OUTGO
)
SELECT ORR.DATE_OUTGO AS OT_DATE_OUTGO, 
       CASE 
          WHEN X.STOCK_RUNTOTAL - ORDER_RUNTOTAL >= 0 THEN 0  
          ELSE X.STOCK_RUNTOTAL - ORDER_RUNTOTAL
       END AS DIFF
FROM ORDER_RUN AS ORR
OUTER APPLY (
   SELECT TOP 1 STOCK_RUNTOTAL
   FROM STOCK_RUN AS SR
   WHERE STOCK_RUNTOTAL >= ORDER_RUNTOTAL -- Stop if stock quantity has exceeded order quantity
         OR
         STOCK_RUNTOTAL = TOTAL_STOCK     -- Stop if the end of stock has been reached
   ORDER BY SR.DATE_OUTGO) AS X