So you want to take your begin+end ranges and, where there's an overlap, split this into separate start+end ranges?
I'm not sure what your logic is for deciding exactly what the start + end dates are. But here's something that may help you:
- First convert the start & ends into a single list of dates. You can do this with unpivot
- Get all the distinct dates from this
- Turn this back into start+end ranges by taking the current date and finding the next using lead.
Which gives something like:
drop table Emp_Dates;
create table Emp_Dates (
Emp_Id varchar2(10),
Begin_Date date,
End_Date date
);
Insert into Emp_Dates Values ('3353', to_date('3/1/2015','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('10/31/2009','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('10/31/2016','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('7/1/2014','mm/dd/yyyy'),to_date('9/30/2014','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('1/1/2011','mm/dd/yyyy'),to_date('6/30/2014','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('10/1/2013','mm/dd/yyyy'),to_date('10/31/2016','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('11/1/2009','mm/dd/yyyy'),to_date('9/30/2013','mm/dd/yyyy'));
Insert into Emp_Dates Values ('13221', to_date('8/1/2009','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('13221', to_date('12/1/2014','mm/dd/yyyy'),to_date('2/28/2015','mm/dd/yyyy'));
Insert into Emp_Dates Values ('13221', to_date('8/1/2009','mm/dd/yyyy'),to_date('3/31/2016','mm/dd/yyyy'));
with dts as (
select * from emp_dates
unpivot ( dt for src in (begin_date, end_date) )
) , distinct_dts as(
select distinct emp_id, dt from dts
) , ranges as (
select emp_id, dt start_date,
lead(dt) over (partition by emp_id order by dt) end_date
from distinct_dts
)
select * from ranges
where end_date is not null
order by emp_id desc, start_date;
EMP_ID START_DATE END_DATE
3353 01-AUG-2009 00:00:00 31-OCT-2009 00:00:00
3353 31-OCT-2009 00:00:00 01-NOV-2009 00:00:00
3353 01-NOV-2009 00:00:00 01-JAN-2011 00:00:00
3353 01-JAN-2011 00:00:00 30-SEP-2013 00:00:00
3353 30-SEP-2013 00:00:00 01-OCT-2013 00:00:00
3353 01-OCT-2013 00:00:00 30-JUN-2014 00:00:00
3353 30-JUN-2014 00:00:00 01-JUL-2014 00:00:00
3353 01-JUL-2014 00:00:00 30-SEP-2014 00:00:00
3353 30-SEP-2014 00:00:00 01-MAR-2015 00:00:00
3353 01-MAR-2015 00:00:00 31-OCT-2016 00:00:00
3353 31-OCT-2016 00:00:00 31-DEC-9999 00:00:00
13221 01-AUG-2009 00:00:00 01-DEC-2014 00:00:00
13221 01-DEC-2014 00:00:00 28-FEB-2015 00:00:00
13221 28-FEB-2015 00:00:00 31-MAR-2016 00:00:00
13221 31-MAR-2016 00:00:00 31-DEC-9999 00:00:00
This gives ranges where the previous end = current start (i.e. proper ranges! ;) You can jigger this by subtracting from the end dates if necessary.
If you're still stuck, please give a detailed (non-code) explanation of how you determine what makes a new start+end range.