How to get running total from consecutive columns in Oracle SQL

Question

I have troubles to display consecutive holidays from an existing date dataset in Oracle SQL. For example, in December 2017 between 20th and 30th, there are the following days off (because Christmas and weekend days):

• 23.12.2017 Saturday
• 24.12.2017 Sunday
• 25.12.2017 Christmas
• 30.12.2017 Saturday

Now I want my result dataset to look like this (RUNTOT is needed):

DAT         ISOFF   RUNTOT
20.12.2017  0       0
21.12.2017  0       0
22.12.2017  0       0
23.12.2017  1       1
24.12.2017  1       2
25.12.2017  1       3
26.12.2017  0       0
27.12.2017  0       0
28.12.2017  0       0
29.12.2017  0       0
30.12.2017  1       1

That means when "ISOFF" changes I want to count (or sum) the consecutive rows where "ISOFF" is 1.

I tried to approach a solution with an analytic function, where I summarize the "ISOFF" to the current row.

  SELECT DAT,
         ISOFF,
         SUM (ISOFF)
         OVER (ORDER BY DAT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             AS RUNTOT
    FROM (TIME_DATASET)
   WHERE DAT BETWEEN DATE '2017-12-20' AND DATE '2017-12-27'
ORDER BY 1

What I get now is following dataset:

DAT         ISOFF   RUNTOT
20.12.2017  0       0
21.12.2017  0       0
22.12.2017  0       0
23.12.2017  1       1
24.12.2017  1       2
25.12.2017  1       3
26.12.2017  0       3
27.12.2017  0       3
28.12.2017  0       3
29.12.2017  0       3
30.12.2017  1       4

How can I reset the running total if ISOFF changes to 0? Or is this the wrong approach to solve this problem?

Thank you for your help!

Answer 1

This is a gaps-and-islands problem. Here is one method that assigns the groups by the number of 0s up to that row:

select t.*,
       (case when is_off = 1
             then row_number() over (partition by grp order by dat)
        end) as runtot
from (select t.*,
             sum(case when is_off = 0 then 1 else 0 end) over (order by dat) as grp
      from TIME_DATASET t
     ) t;