Home > PL/SQL > Extraction of consecutive periods (without regard to the OverLap)

Extraction of consecutive periods (without regard to the OverLap)

Thursday, 18 August, 2011 Leave a comment Go to comments

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;
Advertisements
Categories: PL/SQL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: