Converting date range to multiple period

Question

I have table like below

ID StartDate    EndDate
1  15-MAR-2009  8-DEC-2011
2  14-JAN-2010  18-FEB-2013

I need output something like this -

ID  StartDate    EndDate
1   15-MAR-2009  31-DEC-2009
1   1-JAN-2010   31-DEC-2010
1   1-JAN-2011   8-Dec-2011
2   14-JAN-2010  31-DEC-2010
2   1-JAN-2011   31-DEC-2011
2   1-JAN-2012   31-DEC-2012
2   1-JAN-2013   18-FEB-2013

I have 9i oracle as database, I would perfer to do this with informatica but seems difficult to find solution there, cant create a procedure so need sql query. If anyone can provide in informatica 8.6 then that would be even great.

Edited :-

Create table test_range
(Sr_No number,
start_date date,
end_date date)

create table yr_range
(years date);

Insert statements :-

insert into test_range values (1,'15-MAR-2009','8-DEC-2011');
insert into test_range values (2,'14-JAN-2010','18-FEB-2013');

insert into yr_range values ('31-Dec-2005');
insert into yr_range values ('31-Dec-2006');
insert into yr_range values ('31-Dec-2007');
insert into yr_range values ('31-Dec-2008');
insert into yr_range values ('31-Dec-2009');
insert into yr_range values ('31-Dec-2010');
insert into yr_range values ('31-Dec-2011');
insert into yr_range values ('31-Dec-2012');
insert into yr_range values ('31-Dec-2013');

As suggested in one of the answer, I have created another table to get desired output, is it possible using one table only(i.e. test_range)? I m want exploer all the options before finalizing the one.

Answer 1

If you had something like a years dimension, where each row represented a year, then you would join with it based on whether the year falls within the range. This will give you the number of rows you need, and then for each row you conditionally output the start/end ranges. This should also handle cases where your range is less than a year and thus would not be split up.

y.Year is assumed to be an integer.

See working example here: http://sqlfiddle.com/#!4/d4589/13/11

Create table test_range
(Sr_No number,
StartDate date,
EndDate date);

create table yr_range
(years number);

insert into test_range values (1,'15-MAR-2009','8-DEC-2011');
insert into test_range values (2,'14-JAN-2010','18-FEB-2013');

insert into yr_range values ('2005');
insert into yr_range values ('2006');
insert into yr_range values ('2007');
insert into yr_range values ('2008');
insert into yr_range values ('2009');
insert into yr_range values ('2010');
insert into yr_range values ('2011');
insert into yr_range values ('2012');
insert into yr_range values ('2013');


select
r.Sr_No,
CASE
  When to_char( r.StartDate, 'yyyy') = y.years Then r.StartDate
  Else to_date( y.years || '/01/01', 'yyyy/mm/dd')  
END as StartDate,
CASE
  When to_char( r.EndDate , 'yyyy') = y.years Then r.EndDate
  Else to_date( y.years || '/12/31', 'yyyy/mm/dd')  
END as EndDate 
from test_range r, yr_range y 
where
      to_char( r.StartDate, 'yyyy') <= y.years  
  AND to_char( r.EndDate, 'yyyy')  >= y.years;

You could also generate your years sequence using some of these techniques: ORACLE SQL:Get all integers between two numbers