Fetching last manager name and number for the terminated employees in sql

Nirmal Kumar L

Summary

Fetching last manager name and number for the terminated employees in sql

Content

Hi,

 

Anyone know how to fetch the manager name for the terminated employees? For terminated or resigned employees manager name and number wont  be there in application. But our customer requirement is to fetch the manager details for them. They want to capture his/her manager name before he resigned. How to fetch that in sql?

 

Thanks in advance. Need your inputs.

Nirmal kumar

Nirmal Kumar L

select distinct per.person_number ter_person_number,

      pos.actual_termination_date ter_actual_termination_date,

      s.manager_id ter_manager_id,

      mgr_num.person_number ter_manager_number,

 per.person_id ter_person_id,

 mgr_name.DISPLAY_NAME ter_manager_name,

 mgr_adid.attribute3 ter_mgr_adid

from per_periods_of_service pos

,    per_assignment_supervisors_f s

,    per_all_people_f mgr_num

,    per_all_people_f per

, PER_PERSON_NAMES_F mgr_name

, per_persons mgr_adid

where pos.actual_termination_date is not null

and   pos.person_id = s.person_id

and   pos.actual_termination_date between s.effective_start_date and s.effective_end_date

and   s.manager_id = mgr_num.person_id

and   s.manager_type = 'LINE_MANAGER'

and   pos.actual_termination_date between mgr_num.effective_start_date and mgr_num.effective_end_date

and   per.person_id = pos.person_id

and   pos.actual_termination_date between per.effective_start_date and per.effective_end_date

and   s.manager_id = mgr_name.person_id

AND mgr_adid.person_id      = mgr_name.person_id(+)