Split a row into multiple rows based on a Date Range

UDA
Enthusiast

Split a row into multiple rows based on a Date Range

Hi,

I am selecting data from a table which has Start Date and End Date as follows

Emp Start Date End Date
10 01/01/2006 12/31/2006
20 05/01/2005 12/31/2005

I want to results as follows
Emp Months
10 Jan 06
10 Feb 06
10 Mar 06
.
.
.
10 Dec-06
20 May-05
20 Jun-05
.
.
.
20 Dec-05

Basically want to split a employee record into each month that the employee was present.

Please help

Sam
4 REPLIES
Junior Contributor

Re: Split a row into multiple rows based on a Date Range

Hi Sam,

create table sam
(Emp int, Start_date Date, End_date Date);

insert into sam values(10, date '2006-01-01', date '2006-12-31');
insert into sam values(20, date '2005-05-01', date '2005-12-31');

/*** instead of sys_calendar.calendar use your company's business calendar ***/
select
emp,
trim(calendar_date (format 'mmm-yy'))
from sam cross join
sys_calendar.calendar c
where calendar_date between start_date and end_date
and day_of_month = 1
order by
emp,
calendar_date
;

/*** In V2R6 there's an nice recursive solution with a "Commmon Table Expression" ***/
with recursive cte (emp, start_date, end_date) as
(
select
emp,
start_date,
end_date
from sam

union all

select
emp,
add_months(start_date, 1) as newstart,
end_date
from cte
where newstart < end_date
)
select
emp,
trim(start_date (format 'mmm-yy'))
from cte
order by
emp,
start_date
;

Dieter
Enthusiast

Re: Split a row into multiple rows based on a Date Range

Thanks Dieter!!!

Both the sql's work gr8.

From the performance perspective which one is the better of the 2.

Thanks,
Sam

Junior Contributor

Re: Split a row into multiple rows based on a Date Range

Hi Sam,
i can't tell you which is more efficient, you'll have to test it with your data.

It probably depends on:

number of rows in your calendar table to be cross joined
vs.
number of recursion levels

Dieter
Enthusiast

Re: Split a row into multiple rows based on a Date Range

Thanks Once Again!

Those sql's were damn good!