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!

score 3 · Accepted Answer · answered Apr 18 '19 at 13:02

3

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;

answered Apr 18 '19 at 13:02

Gordon Linoff

1,242,037
58
646
786

hi @gordon, what about to take fake date for each group? https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f45e214882e5920ca674d2cb0165847b May be is the same cost (?) – dani herrera Apr 18 '19 at 13:49
Someone should post a generic "I have a gaps-and-islands problem" question so we can answer it and mark all future questions like this duplicates. It seems like this same problem comes up a lot. – Matthew McPeak Apr 18 '19 at 14:09
@MatthewMcPeak . . . There is not a generic solution. – Gordon Linoff Apr 18 '19 at 18:12
@dani . . . I don't know what a fake date is. Perhaps that is for another question. – Gordon Linoff Apr 18 '19 at 18:13
@GordonLinoff, I said 'fake date' as 'random date', just a date taked as value to identify the group (island). Just follow the link on previous comment. – dani herrera Apr 18 '19 at 18:25
1

@MatthewMcPeak, also, to tag the question as ghap-and-island can helps. They are a tag ready for it. You have 10k, be free to retag t'he Q. – dani herrera Apr 18 '19 at 18:27
@GordonLinoff Thank you for your help. Your query solved my problem. – Ivo Apr 24 '19 at 10:43

score 2 · Answer 2 · answered Apr 18 '19 at 14:04

You may use the recursive recursive subquery factoring - the precondition is, that your dates are consecutive without gaps (or you have some oder row number sequence to follow in steps of one).

WITH t1(dat, isoff, runtot) AS (
  SELECT dat, isoff, 0 runtot
  FROM   tab 
  WHERE  DAT = DATE'2017-12-20'
  UNION ALL
  SELECT t2.dat, t2.isoff,  
          case when t2.isoff = 0 then 0 else runtot + t2.isoff end as runtot
  FROM   tab t2, t1
  WHERE  t2.dat = t1.dat + 1
)
SELECT  dat, isoff, runtot
FROM   t1;

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

score 1 · Answer 3 · answered Apr 18 '19 at 14:24

Another variation, which doesn't need a subquery or CTE but does need all days to be present and have the same time, is - for the holiday dates only (where isoff = 1) - to see how many days it's been since the last non-holiday date:

select dat,
  isoff,
  case
    when isoff = 1 then
      coalesce(dat - max(case when isoff = 0 then dat end)
              over (order by dat range between unbounded preceding and 1 preceding), 1)
    else 0
  end as runtot
from time_dataset
order by dat;

DAT             ISOFF     RUNTOT
---------- ---------- ----------
2017-12-20          0          0
2017-12-21          0          0
2017-12-22          0          0
2017-12-23          1          1
2017-12-24          1          2
2017-12-25          1          3
2017-12-26          0          0
2017-12-27          0          0
2017-12-28          0          0
2017-12-29          0          0
2017-12-30          1          1

The coalesce() is there in case the first date in the range is a holiday - as there is no previous non-holiday date to compare against, that subtraction would get null.

db<>fiddle with a slightly larger data set.

How to get running total from consecutive columns in Oracle SQL

3 Answers3