Oracle SQL Reset Running Sum When Zero

Question

I imagine this is possible, but extensive research and countless hours haven't paid off.

I'm selling product out of a warehouse. I know when I will be receiving shipments and I have a daily sales forecast (in quantity). I need to calculate the ending inventory. It's basically a running sum of Inventory - Forecast.

The issue is, when I run out of product, I won't have negative inventory the following day, as a running sum would suggest. Inventory will be zero until I receive another shipment. It can go down to zero multiple times in the forecast (far more than in the example below).

Visual of the dataset (desired column in yellow)

SQL Fiddle for cracking the case

Here is my actual query:

SELECT FORECAST_DATE, DAYS_OUT, INBOUND_INVENTORY, FORECAST,
       ENDING_INVENTORY AS DESIRED_RESULT,
       SUM(INBOUND_INVENTORY) OVER (PARTITION BY NULL ORDER BY DAYS_OUT) -
           SUM(FORECAST) OVER (PARTITION BY NULL ORDER BY DAYS_OUT) AS ENDING_INVENTORY
FROM MRP

Columns

Date: The forecast date (starting today)
Days Out: The number of days between today and the forecast date
Inbound Inventory: Product coming in (for today, product here)
Forecast: My projected sales quantity
Ending Inventory: Inbound Inventory - Forecast + If yesterday's Ending Inventory <= 0 then 0 else yesterday's Ending Inventory.

Answer 1

Here is a solution using the MODEL clause (introduced in Oracle 10).

I didn't include the forecast_date column - days_out is enough.

with
     inputs ( days_out, inbound_inventory, forecast ) as (
       select  0,  24,   0 from dual union all
       select  1,   0, 124 from dual union all
       select  2,   0, 154 from dual union all
       select  3,   0, 316 from dual union all
       select  4, 780, 119 from dual union all
       select  5, 780, 148 from dual union all
       select  6, 780, 123 from dual union all
       select  7,   0, 168 from dual union all
       select  8,   0, 323 from dual union all
       select  9,   0, 184 from dual union all
       select 10,   0, 331 from dual union all
       select 11,   0, 149 from dual union all
       select 12,   0, 431 from dual union all
       select 13,   0, 153 from dual union all
       select 14,   0, 183 from dual union all
       select 15,   0, 169 from dual union all
       select 16,   0, 169 from dual union all
       select 17, 780, 331 from dual
     )
select days_out, inbound_inventory, forecast, ending_inventory
from   inputs
model
  dimension by ( days_out )
  measures     ( inbound_inventory, forecast, 0 ending_inventory )
  rules update 
  iterate(1000000) until (previous(ending_inventory[iteration_number + 1]) is null)
  (
    ending_inventory[iteration_number] = 
           greatest ( 0, inbound_inventory[cv()] - forecast[cv()] 
                           + nvl(ending_inventory[cv() - 1], 0 )
                    )
  )
;

Output:

  DAYS_OUT INBOUND_INVENTORY   FORECAST ENDING_INVENTORY
---------- ----------------- ---------- ----------------
         0                24          0               24
         1                 0        124                0
         2                 0        154                0
         3                 0        316                0
         4               780        119              661
         5               780        148             1293
         6               780        123             1950
         7                 0        168             1782
         8                 0        323             1459
         9                 0        184             1275
        10                 0        331              944
        11                 0        149              795
        12                 0        431              364
        13                 0        153              211
        14                 0        183               28
        15                 0        169                0
        16                 0        169                0
        17               780        331              449

18 rows selected.

Answer 2

Could this be correct? It is something called recursive common table expression.

WITH
cte_mrp as
(
    Select row_number() over (partition by null order by forecast_date) as line, mrp.*
    From mrp
),
RCTE (line, forecast_date, days_out, inbound_inventory, forecast, /*iteration, anchor,*/ ending_inventory) as
(
    Select line, forecast_date, days_out, inbound_inventory, forecast, /*0 iteration, 'anchor' anchor,*/
        CASE WHEN inbound_inventory-forecast < 0 THEN 0 ELSE inbound_inventory-forecast END ending_inventory
    From cte_mrp
    Where line = 1

    union all

    Select m.line, m.forecast_date, m.days_out, m.inbound_inventory, m.forecast, /*r.iteration + 1, 'rcte' anchor,*/
        CASE WHEN r.ending_inventory+m.inbound_inventory - m.forecast < 0 THEN 0 ELSE r.ending_inventory+m.inbound_inventory - m.forecast END ending_inventory
    From cte_mrp m
    Inner join rcte r on (r.line = (m.line-1))
)

Select * From RCTE;