General OBIEE

Get Involved. Join the Conversation.

Topic

    Nirmal Kumar L
    Fetching last manager name and number for the terminated...Answered
    Topic posted September 16, 2017 by Nirmal Kumar LBronze Medal: 1,250+ Points, tagged BI Publisher, BI Publisher Data Models, Data Models, OBI Answers, OBIEE Answers, Reports, Sample Reports, SQL 
    68 Views, 1 Comment
    Title:
    Fetching last manager name and number for the terminated employees in sql
    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

    Best Comment

    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(+)

    Comment

     

    • 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(+)