CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Feres.o
Feres.o

Reputation: 283

dividing a period according to a time interval

I have a table which contains multiple periods of time which represent the 'person-career', in the same table I have all interruption related to the same person ( i.e holidays, illnesses ...).

the used order withn the table is ( order by codeMission, then year , then Working type first then Holidays period second) 

 Date begin Date end   Type    codeMission  year  name
 ---------- --------------------------------------------
 2001-01-01 2001-12-31 Working     Tx100     2001  James
 2001-05-01 2001-05-12 holidays    Tx100     2001  James

I would like to split the working period by the holidays times

 Date begin Date end   Type    codeMission  year  name
 ---------- -----------------------------------------
 2001-01-01 2001-04-30 Working    Tx100     2001  James
 2001-05-01 2001-05-12 holiday    Tx100     2001  James
 2001-05-13 2001-12-31 Working    Tx100     2001  James

Is there a general way to do that? As you know, the holidays/illnesses time can be sometimes before some time and after some time.

question 1 : How can i get this format with all columns ? and there is a specific i would like to mention here , question 2 : when the period of holidays is defined on two years. example:

Date begin Date end   Type    codeMission  year  name
 ---------- --------------------------------------------
 2001-01-01 2001-12-31 Working     Tx100     2001  James
 2001-10-11 2002-04-30 holidays    Tx100     2001  James
 2002-01-01 2001-12-31 Working     Tx100     2002  James

here we will get multiple issues !!! the divided periods are not correct !! what do you thing ?

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is rather complicated. The idea is to "unpivot" the dates and keep track of the type going in or out on each day. Then you can use cumulative sums to keep track of the actual type.

So:

with t as (
      select date '2001-01-01' as sdate, date '2001-12-31' as edate, 'Working' as type from dual union all
      select date '2001-05-01' as sdate, date '2001-05-12' as edate, 'holidays' from dual
     ),
     d as (
      select sdate as dte, type, 1 as flag from t union all
      select edate + 1, type, -1 as flag from t
     )
select d.dte, lead(dte) over (order by dte) - 1 as enddte,
       (case when flag = -1 then lag(type) over (partition by grp order by dte) else type end) as type
from (select d.*, sum(flag) over (order by dte) as grp
      from d
     ) d
order by dte;

Here is a db<>fiddle.

EDIT:

Here is an improved version:

with t as (
      select date '2001-01-01' as sdate, date '2001-12-31' as edate, 'Working' as type from dual union all
      select date '2001-05-01' as sdate, date '2001-05-12' as edate, 'holidays' from dual union all
      select date '2001-07-01' as sdate, date '2001-08-12' as edate, 'holidays' from dual
     ),
     d as (
      select sdate as dte from t union all
      select edate + 1 from t
     )
select d.dte, d.enddte,
       (select max(t2.type) keep (dense_rank first order by t2.sdate desc)
        from t t2
        where t2.sdate <= d.dte and t2.edate >= d.enddte
       ) as type 
from (select d.*, lead(dte) over (order by dte) - 1 as enddte
      from d
     ) d
where dte <= enddte
order by d.dte

This approach simply gets the individual periods (by breaking up the original periods). Then for each period, it finds the type that is valid during that period. That is the most recent record that covers the entire period.

Note that because the dates are "adjacent", there is no partial coverage of the periods.

Upvotes: 4

Related Questions