Reset a cumulative sum?

Question

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.

Answer 1

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.

Answer 2

First we need to divide rows to groups. In this case you can use the count of 0 values before current row as a group number. And then you can use SUM() OVER for these groups. To get 0 for OOS = 0 you can use CASE statement or just OOS*SUM(OOS) as soon as OOS = (0,1)

Something like this:

select T1.*,

OOS*SUM(OOS) OVER (PARTITION BY GRP ORDER BY S_DATE) CUMLATIVE_DAYS_OOS

FROM
(
select T.*,
(select count(*) from STK where S_Date<T.S_Date and OOS=0) GRP
FROM STK T
)  T1 
ORDER BY S_Date

SQLFiddle demo