Valid for mulple asg
Valid for muliple hire date
select *
from
(
select ass.assignment_id, ass.effective_start_date start_date, ass.effective_end_date end_date, ass2.effective_start_date prev_start_date, ass2.effective_end_date prev_end_date
from per_assignments_f ass,
per_people_f per,
per_assignments_f ass2
where 1 = 1
and ass.person_id = per.person_id
and ass.effective_start_date between per.effective_start_date and per.effective_end_date
and ass.assignment_id = ass2.assignment_id (+)
and ass2.effective_end_date (+) = ass.effective_start_date - 1
and ass.assignment_type = 'E' -- No Applicants
and ass.effective_start_date <> -- No First record of assignment (Valid for primary and secondary Asg)
(
Select min(effective_start_date)
from per_assignments_f ass3
where ass3.assignment_id = ass.assignment_id
)
order by ass.effective_start_date
)
where prev_start_date is null
