SQL & PL/SQL

SQL> create table t (mer_sid,from_date,to_date)
  2  as
  3  select '00999', date '2008-01-01', date '2008-01-31' from dual union all
  4  select '00999', date '2008-02-01', date '2008-02-29' from dual union all
  5  select '00999', date '2008-04-01', date '2008-04-30' from dual union all
  6  select '00999', date '2008-05-01', date '2008-05-31' from dual union all
  7  select '00999', date '2008-06-01', date '2008-06-30' from dual union all
  8  select '00888', date '2008-02-01', date '2008-02-29' from dual
  9  /

Table created.

SQL> select mer_sid
  2       , min(from_date) from_date
  3       , max(to_date) to_date
  4    from ( select mer_sid
  5                , from_date
  6                , to_date
  7                , max(grp) over (partition by mer_sid order by from_date) maxgrp
  8             from ( select mer_sid
  9                         , from_date
 10                         , to_date
 11                         , case lag(to_date) over (partition by mer_sid order by from_date)
 12                           when from_date - 1 then null
 13                           else rownum
 14                           end grp
 15                      from t
 16                  )
 17         )
 18   group by mer_sid
 19       , maxgrp
 20   order by mer_sid desc
 21       , from_date
 22  /

MER_S FROM_DATE           TO_DATE
----- ------------------- -------------------
00999 01-01-2008 00:00:00 29-02-2008 00:00:00
00999 01-04-2008 00:00:00 30-06-2008 00:00:00
00888 01-02-2008 00:00:00 29-02-2008 00:00:00

3 rows selected.

select mer_sid , min(FROM_DATE) FROM_DATE , max(TO_DATE) TO_DATE
 from (select mer_sid,FROM_DATE,TO_DATE,next_date,grp
           from t
           model
           partition by (mer_sid)
          dimension by (row_number()over(partition by mer_sid order by FROM_DATE)   rn)
          measures( FROM_DATE,TO_DATE,lag(to_DATE)over(partition by mer_sid order by 
                             from_date ) next_date, 1 grp,1 grpprec)
          rules
          upsert
          iterate (1000)
          until(presentv(FROM_DATE[iteration_number+2],1,0)=0)
          (  grpprec[any]=nvl(grp[cv()-1],1),
             grp[any]= case when nvl( next_date[cv()]+1,from_DATE[cv()]) =from_DATE[cv()]  then                
             grpprec[cv()] else  grpprec[cv()]+1  end))
group by mer_sid ,grp
/

SQL> select * from t;

MER_S FROM_DATE TO_DATE
----- --------- ---------
00999 01-JAN-08 31-JAN-08
00999 01-FEB-08 29-FEB-08
00999 01-APR-08 30-APR-08
00999 01-MAY-08 31-MAY-08
00999 01-JUN-08 30-JUN-08
00888 01-FEB-08 29-FEB-08

6 rows selected.

SQL> select mer_sid , min(FROM_DATE) FROM_DATE , max(TO_DATE) TO_DATE
  2   from (select mer_sid,FROM_DATE,TO_DATE,next_date,grp
  3             from t
  4             model
  5             partition by (mer_sid)
  6            dimension by (row_number()over(partition by mer_sid order by FROM_DATE)   rn)
  7            measures( FROM_DATE,TO_DATE,lag(to_DATE)over(partition by mer_sid order by 
  8                               from_date ) next_date, 1 grp,1 grpprec)
  9            rules
 10            upsert
 11            iterate (1000)
 12            until(presentv(FROM_DATE[iteration_number+2],1,0)=0)
 13            (  grpprec[any]=nvl(grp[cv()-1],1),
 14               grp[any]= case when nvl( next_date[cv()]+1,from_DATE[cv()]) =from_DATE[cv()]  then
                
 15               grpprec[cv()] else  grpprec[cv()]+1  end))
 16  group by mer_sid ,grp
 17  / 

MER_S FROM_DATE TO_DATE
----- --------- ---------
00999 01-JAN-08 29-FEB-08
00999 01-APR-08 30-JUN-08
00888 01-FEB-08 29-FEB-08
 

with t as(
 select '00999' mer_sid, date '2008-01-01' as from_date, date '2008-01-31' as to_date from dual union all
 select '00999', date '2008-02-01', date '2008-02-29' from dual union all
 select '00999', date '2008-04-01', date '2008-04-30' from dual union all
 select '00999', date '2008-05-01', date '2008-05-31' from dual union all
 select '00999', date '2008-06-01', date '2008-06-30' from dual union all
 select '00888', date '2008-02-01', date '2008-02-29' from dual)
select mer_sid,min(from_date),max(to_date)
from (select mer_sid,from_date,to_date,gid
        from t
       model
      partition by(mer_sid)
      dimension by(Row_Number() over(partition by mer_sid order by from_date) as soeji)
      measures(from_date,to_date,0 as willsum,0 as gid)
      rules(
      willSum[any] = decode(from_date[cv()],to_date[cv()-1]+1,0,1),
      gid[any] = sum(willSum)[cv() >= soeji]))
group by mer_sid,gid
order by mer_sid desc,gid;

MER_S  MIN(FROM  MAX(TO_D
-----  --------  --------
00999  08-01-01  08-02-29
00999  08-04-01  08-06-30
00888  08-02-01  08-02-29