Как рассчитать текущий итог за конкретную дату или лучше?

Я хотел бы вычислить, какие заказы могут быть выполнены и какие даты отсутствуют ( diff) после завершения как можно большего количества заказов на данный момент. Выбирается в порядке FEFO .

Когда я думаю о проблеме, я думаю, что некоторая текущая сумма, основанная как на датах акций, так и на ордерах, была бы одним из способов пойти. На основе вычислить общий ход / баланс хода и других подобных потоков это кажется хорошим решением проблемы - но Я открыт для других решений.

Пример кода 

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

Результаты 

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

я убрал сложности, такие как номера товаров, различные требования одновременно (используя только точную дату и дату или лучше) и т. д. чтобы максимально упростить основной вопрос.

Правка 1:

Обновленные строки как в таблицах, так и в результатах (корректные и с исходным запросом). Первый ответ дал разницу -12 на 2017-08-25 вместо 0. Но 2017-08-26 было правильно.

1 4
sql-server tsql sql-server-2016

1 ответ:

Вы можете использовать следующий запрос: 

;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

Первый CTE вычисляет общий ход заказа, в то время как второй CTE вычисляет общий ход запаса. Запрос использует OUTER APPLY, чтобы получить общее количество запасов до даты, когда был сделан текущий заказ.

Редактировать:

Если вы хотите использовать запас дат, которые приходят в будущем относительно даты заказа, то просто замените: 

WHERE SR.DATE_OUTGO <= ORR.DATE_OUTGO

С 

WHERE STOCK_RUNTOTAL <= ORDER_RUNTOTAL

В OUTER APPLY операция.

Править 2:

Следующий улучшенный запрос должен, наконец, решить проблему: 

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