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.

Giorgos Betsos · Accepted Answer · 2017-08-28T21:49:00.787

2

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

edited Aug 28 '17 at 21:49

answered Aug 28 '17 at 08:28

Giorgos Betsos

71,379
9
63
98

It give `-26` on `2017-08-26` – Ruslan K. Aug 28 '17 at 08:31
Yes, I was just gonna write that @RuslanK. It seems that it matches the dates exactly instead of "this or better". So that the stock with date_outgo 2017-08-27 goes untouched. – Danieboy Aug 28 '17 at 08:36
@Danieboy I'm afraid I've not understood that *this is better part*. Can you elucidate on this issue? I mean how can an order made on a specific date consume the stock of *future* stock additions? – Giorgos Betsos Aug 28 '17 at 08:39
For example. The order 4 is ordered on **2017-08-26** or *better*. So if there are nothing in stock with date **2017-08-26** you can pick from any future date. So in this case you can pick **order 4** from batch **1002** with date **2017-08-27** because it's "better" (later) than the ordered date. Does that clear the confusion up? – Danieboy Aug 28 '17 at 08:43
Upon further investigation I've found that the result is not correct in another case, but close! I've edited the question. Is there any chance that you could look at it? Thanks in advance. – Danieboy Aug 28 '17 at 12:20
I think the issue is that the query doesn't take into account that the orders with **2017-08-25** can be picked from any later date in the cross apply, but the grand total gets correct in the end. So the diff of **-12** at **2017-08-25** should be **0** and the total in the end at **2017-08-26** is in fact, as the query gives, **-18**. But then the follow-up issue is that if we correct the prior - the sum of the total (on the latest date) might become incorrect. – Danieboy Aug 28 '17 at 13:20
It seems with the last edit this solutions is working. And I was also able to add multiple more complexities to the query without breaking it. Thanks! – Danieboy Aug 29 '17 at 09:04

How to calculate running total over specific date or better?

Example code

Results

Edit 1:

1 Answers1