Как рассчитать промежуточную сумму за определенную дату или лучше?

Я хотел бы рассчитать, какие заказы могут быть выполнены и какие даты отсутствуют (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 на 25 августа 2017 г. вместо 0. Но 26 августа 2017 г. был правильным.


sql-server tsql sql-server-2016
person Danieboy    schedule 28.08.2017    source источник

Ответы (1)


arrow_upward
2
arrow_downward

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

;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
person Giorgos Betsos    schedule 28.08.2017
comment
Это дает -26 на 2017-08-26 - person Ruslan K.; 28.08.2017
comment
Да, я как раз собиралась написать это @RuslanK. Кажется, что он точно соответствует датам, а не этому или лучше. Так что сток с date_outgo 2017-08-27 остался нетронутым. - person Danieboy; 28.08.2017
comment
@Daniboy Боюсь, я не понял, что это лучшая часть. Можете просветить по этому вопросу? Я имею в виду, как заказ, сделанный в определенную дату, может потреблять запас будущих пополнений запасов? - person Giorgos Betsos; 28.08.2017
comment
Например. Заказ 4 оформлен 2017-08-26 или лучше. Поэтому, если в наличии нет товаров с датой 2017-08-26, вы можете выбрать любую дату в будущем. Таким образом, в этом случае вы можете выбрать заказ 4 из партии 1002 с датой 2017-08-27, потому что она лучше (позже), чем дата заказа. . Это устраняет путаницу? - person Danieboy; 28.08.2017
comment
При дальнейшем исследовании я обнаружил, что в другом случае результат неверен, но близок! Я отредактировал вопрос. Есть ли шанс, что вы могли бы посмотреть на это? Заранее спасибо. - person Danieboy; 28.08.2017
comment
Я думаю, проблема в том, что запрос не учитывает, что заказы с 2017-08-25 могут быть выбраны с любой более поздней даты в перекрестной заявке, но общая сумма получается правильной в конец. Таким образом, разница -12 на 2017-08-25 должна быть 0, а итоговая сумма в конце на 2017-08- 26 на самом деле, как показывает запрос, -18. Но тогда последующая проблема заключается в том, что если мы исправим предыдущую сумму, общая сумма (на последнюю дату) может стать неправильной. - person Danieboy; 28.08.2017
comment
Кажется, с последним редактированием это решение работает. И я также смог усложнить запрос, не нарушая его. Спасибо! - person Danieboy; 29.08.2017