How to calculate running total over specific date or better?

Question

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.

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.

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

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.

Edit 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