Reset total amount?

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 

I want too:

 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 still have 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 result:

 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 

This is close to what I want, but it is clear that the amount is ongoing. Is it possible to reset to collect this total amount and run it again?

I tried searching in stackoverflow and google, but I'm not quite sure what I should search.

Any help is greatly appreciated.

Answer 1

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

 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 | 

You can then judge this oos, broken down by the 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 on sqlfiddle .

Answer 2

First we need to split the lines into groups. In this case, you can use the value 0 for the current line as the group number. And then you can use SUM() OVER for these groups. To get 0 for OOS = 0 , you can use the CASE statement or just OOS*SUM(OOS) as soon as OOS = (0,1)

Something like that:

 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