Reset a cumulative sum?

oracle

Solution

You need to identify groups of consecutive days where oos = 1 or 0. This can be done by using LAG function to find when oos column changes and then summing over it.

with x (s_date,qty,oos,chg) as (
  select s_date,qty,oos,
         case when oos = lag(oos,1) over (order by s_date)
                then 0
                else 1
         end
  from stk
  )
select s_date,qty,oos,
       sum(chg) over (order by s_date) grp
from x;

output :

|                         S_DATE | QTY | OOS | GRP |
|--------------------------------|-----|-----|-----|
| January, 01 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 02 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 03 2013 00:00:00+0000 |   0 |   1 |   1 |
| January, 04 2013 00:00:00+0000 |   5 |   0 |   2 |
| January, 05 2013 00:00:00+0000 |   0 |   1 |   3 |
| January, 06 2013 00:00:00+0000 |   0 |   1 |   3 |

Then, you can sum over this oos, partitioned by grp column to get consecutive oos days.

with x (s_date,qty,oos,chg) as (
  select s_date,qty,oos,
         case when oos = lag(oos,1) over (order by s_date)
                then 0
                else 1
         end
  from stk
  ),
y (s_date,qty,oos,grp) as (
  select s_date,qty,oos,
         sum(chg) over (order by s_date)
  from x
  )
select s_date,qty,oos,
       sum(oos) over (partition by grp order by s_date) cum_days_oos
from y;

output:

|                         S_DATE | QTY | OOS | CUM_DAYS_OOS |
|--------------------------------|-----|-----|--------------|
| January, 01 2013 00:00:00+0000 |   0 |   1 |            1 |
| January, 02 2013 00:00:00+0000 |   0 |   1 |            2 |
| January, 03 2013 00:00:00+0000 |   0 |   1 |            3 |
| January, 04 2013 00:00:00+0000 |   5 |   0 |            0 |
| January, 05 2013 00:00:00+0000 |   0 |   1 |            1 |
| January, 06 2013 00:00:00+0000 |   0 |   1 |            2 |

Demo at sqlfiddle.

Problem

I have the following dataset (table: stk): ``` S_Date Qty OOS (Out of Stock - 1 true, 0 false) 01/01/2013 0 1 02/01/2013 0 1 03/01/2013 0 1 04/01/2013 5 0 05/01/2013 0 1 06/01/2013 0 1 ``` And what I want is: ``` S_Date Qty Cumulative_Days_OOS 01/01/2013 0 1 02/01/2013 0 2 03/01/2013 0 3 04/01/2013 5 0 -- No longer out of stock 05/01/2013 0 1 06/01/2013 0 2 ``` The closest I've got so far is the following SQL: ``` SELECT S_DATE, QTY, SUM(OOS) OVER (PARTITION BY OOS ORDER BY S_DATE) CUMLATIVE_DAYS_OOS FROM STK GROUP BY S_DATE, QTY, OOS ORDER BY 1 ``` This gives me the following output: ``` S_Date Qty Cumulative_Days_OOS 01/01/2013 0 1 02/01/2013 0 2 03/01/2013 0 3 04/01/2013 5 0 05/01/2013 0 4 06/01/2013 0 5 ``` It is close to what I want, but understandably, the sum is continued. Is it possible to reset this cumulative sum and start it again? I've tried searching around on stackoverflow and google, but I'm not really sure what I should be searching for. Any help much appreciated.

Original source