Matt S Matt S - 2 months ago 11
SQL Question

Oracle SQL Reset Running Sum When Zero

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

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