如何计算特定日期或更好的运行总数？

I would like to calculate the what orders can be completed and what dates are missing (diff) after completing as many orders as possible at the moment. Picked in order of FEFO.

我想计算在完成尽可能多的订单之后可以完成哪些订单以及缺少哪些日期（差异）。按FEFO顺序挑选。

When thinking about the problem I think that some kind of a running sum based on both the dates of the stock and the orders would be one way to go. Based on Calculate running total / running balance and other similar threads it seems like a good fit for the problem - but I'm open to other solutions.

在考虑问题时，我认为基于股票和订单日期的某种运行总和将是一种方法。基于计算运行总计/运行平衡和其他类似线程，它似乎很适合这个问题 - 但我对其他解决方案持开放态度。

Example 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

Results

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

I've taken out complexities like item numbers, different demands simultaneously (using the exact date only and date or better) etc. to simplify the core issue as much as possible.

我已经同时解决了项目编号，不同需求等复杂问题（仅使用确切日期和日期或更好）等，以尽可能简化核心问题。

Edit 1:

Updated rows in both tables and results (correct and with original query). First answer gave a diff of -12 on 2017-08-25 instead of 0. But 2017-08-26 was correct.

更新了表和结果中的行（正确并使用原始查询）。第一个答案在2017-08-25而不是0给出-12的差异。但2017-08-26是正确的。

1 个解决方案

#1

You can use the following query:

您可以使用以下查询：

;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

The first CTE calculates the order running total, whereas the second CTE calculates the stock running total. The query uses OUTER APPLY to get the stock running total up to the date the current order has been made.

第一个CTE计算订单运行总量，而第二个CTE计算库存运行总量。该查询使用OUTER APPLY来获取库存运行总计，直至当前订单的生成日期。

Edit:

编辑：

If you want to consume the stock of dates that come in the future with respect to the order date, then simply replace:

如果您想要使用与订单日期相关的日期库存，那么只需替换：

WHERE SR.DATE_OUTGO <= ORR.DATE_OUTGO

with

同

WHERE STOCK_RUNTOTAL <= ORDER_RUNTOTAL

in the OUTER APPLY operation.

在OUTER APPLY操作中。

Edit 2:

编辑2：

The following improved query should, at last, solve the problem:

以下改进的查询应该最终解决问题：

;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

#1