|
|
|
Forum Member
 
Group: Forum Members
Last Login: 5/19/2008 11:39:33 AM
Posts: 29,
Visits: 19
|
|
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
Sam
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 5/19/2008 11:39:33 AM
Posts: 29,
Visits: 19
|
|
Thanks Dieter!!!
Both the sql's work gr8.
From the performance perspective which one is the better of the 2.
Thanks,
Sam
Sam
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 5/19/2008 11:39:33 AM
Posts: 29,
Visits: 19
|
|
Thanks Once Again!
Those sql's were damn good!
Sam
|
|
|
|