BIP – Extract Length of service

Use the below query to extract the length of service of a worker. In case of rehires this becomes little tricky. Below query makes use of earliest hire date for length of service calculation. If Seniority dates are implemented, then you can avoid using below query:

select papf.person_number, papf.person_id, trunc(months_between(trunc(sysdate),MIN(ppos.date_start))/12,2) LOS
  from per_periods_of_Service ppos
      ,per_all_people_f papf
 where ppos.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
group by papf.person_number, papf.person_id