Just take the above sql's, then you can transform it to any level.
Here are the two combinations (Just watch out for the values under the columns X1 and X2)
demo@ORA11G> with t as (
2 select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
3 to_date('12-07-2013','dd-mm-yyyy') as dto from dual
4 union all
5 select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
6 to_date('12-07-2014','dd-mm-yyyy') as dto from dual )
7 select id, dfrom, dto, min( new_start_dt ), max(new_end_dt) , trunc(new_start_dt,'Y'),
8 greatest( dfrom ,min( new_start_dt ) ) x1,
9 least( dto,max(new_end_dt)) x2
10 from (
11 select id,dfrom,dto, dt new_start_dt ,
12 last_day(dt) as new_end_dt , x
13 from (
14 select * from t
15 model
16 partition by (id)
17 dimension by ( 1 x )
18 measures( cast(null as date) dt ,dfrom,dto )
19 rules iterate(100) until( dt[iteration_number+1] > dto[1] )
20 ( dt[iteration_number+1] = trunc( add_months( dfrom[1] , iteration_number ) ,'mm') ,
21 dfrom[iteration_number+1] = dfrom[1] ,
22 dto[iteration_number+1] = dto[1] )
23 )
24 where dt < dto
25 )
26 group by id, dfrom, dto, trunc(new_start_dt,'Y')
27 order by 1,4 ;
ID DFROM DTO MIN(NEW_ST MAX(NEW_EN TRUNC(NEW_ X1 X2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 12-05-2012 12-07-2013 01-05-2012 31-12-2012 01-01-2012 12-05-2012 31-12-2012
10 12-05-2012 12-07-2013 01-01-2013 31-07-2013 01-01-2013 01-01-2013 12-07-2013
20 12-05-2012 12-07-2014 01-05-2012 31-12-2012 01-01-2012 12-05-2012 31-12-2012
20 12-05-2012 12-07-2014 01-01-2013 31-12-2013 01-01-2013 01-01-2013 31-12-2013
20 12-05-2012 12-07-2014 01-01-2014 31-07-2014 01-01-2014 01-01-2014 12-07-2014
5 rows selected.
demo@ORA11G> with t as (
2 select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
3 to_date('12-07-2013','dd-mm-yyyy') as dto from dual
4 union all
5 select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
6 to_date('12-07-2014','dd-mm-yyyy') as dto from dual)
7 select id,dfrom,dto, dt new_start_dt ,
8 last_day(dt) as new_end_dt , x,
9 case when trunc(dfrom,'Y') = trunc(dt,'Y') then greatest( dfrom, dt)
10 else trunc( greatest( dfrom, dt) ,'Y') end x1 ,
11 case when trunc(dto,'Y') <> trunc(last_day(dt),'Y') then add_months( trunc( least( dto , last_day(dt)),'Y'),12)-1
12 else greatest(dto,last_day(dt)) end x2
13 from (
14 select * from t
15 model
16 partition by (id)
17 dimension by ( 1 x )
18 measures( cast(null as date) dt ,dfrom,dto )
19 rules iterate(100) until( dt[iteration_number+1] > dto[1] )
20 ( dt[iteration_number+1] = trunc( add_months( dfrom[1] , iteration_number*12 ) ,'mm') ,
21 dfrom[iteration_number+1] = dfrom[1] ,
22 dto[iteration_number+1] = dto[1] )
23 )
24 where dt < dto
25 order by id, x ;
ID DFROM DTO NEW_START_ NEW_END_DT X X1 X2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 12-05-2012 12-07-2013 01-05-2012 31-05-2012 1 12-05-2012 31-12-2012
10 12-05-2012 12-07-2013 01-05-2013 31-05-2013 2 01-01-2013 12-07-2013
20 12-05-2012 12-07-2014 01-05-2012 31-05-2012 1 12-05-2012 31-12-2012
20 12-05-2012 12-07-2014 01-05-2013 31-05-2013 2 01-01-2013 31-12-2013
20 12-05-2012 12-07-2014 01-05-2014 31-05-2014 3 01-01-2014 12-07-2014
5 rows selected.
demo@ORA11G>