Extraction of consecutive periods (without regard to the OverLap)
kikanT
ID StaD EndD
-- ---------- ----------
10 2010-06-01 2010-06-12
10 2010-06-13 2010-06-14
10 2010-06-15 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-13
50 2010-11-14 2010-11-20
50 2010-12-22 2010-12-30
Summarized in consecutive periods every ID.
ID StaD EndD
-- ---------- ----------
10 2010-06-01 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-20
50 2010-12-22 2010-12-30
Data creation script
create table kikanT(ID,StaD,EndD) as select 10,date '2010-06-01',date '2010-06-12' from dual union select 10,date '2010-06-13',date '2010-06-14' from dual union select 10,date '2010-06-15',null from dual union select 20,date '2010-06-01',date '2010-06-11' from dual union select 20,date '2010-06-13',date '2010-06-15' from dual union select 50,date '2010-11-11',date '2010-11-13' from dual union select 50,date '2010-11-14',date '2010-11-20' from dual union select 50,date '2010-12-22',date '2010-12-30' from dual;
Solution1
How to use hierarchical queries (> 10g)
select ID,min(connect_by_root StaD) as StaD,EndD
from kikanT
where connect_by_IsLeaf = 1
connect by prior ID=ID
and prior EndD+1=StaD
group by ID,EndD
order by ID,EndD;
Solution2
Analysis of the relevant number
select ID,min(StaD) as StaD,
max(EndD) Keep(Dense_Rank Last order by EndD) as EndD
from (select ID,StaD,EndD,
sum(willSum) over(partition by ID order by StaD) as GID
from (select ID,StaD,EndD,
case when StaD = 1+Lag(EndD) over(partition by ID order by StaD)
then 0 else 1 end as willSum
from kikanT))
group by ID,GID
order by ID,GID;

Recent Comments