Tuesday, February 14, 2017

SQL to find Employee years and months of work experience in PeopleSoft

  SELECT A.EMPLID
, EXTRACT(YEAR FROM (Last_Day(Trunc(SYSDATE)) - LAST_HIRE_DT) YEAR TO MONTH ) EXYR
      , EXTRACT(MONTH FROM (Last_Day(Trunc(SYSDATE)) - LAST_HIRE_DT) YEAR TO MONTH ) EXMON
,A.EFFDT,
A.EMPL_RCD,
A.EFFDT,
A.EFFSEQ,
A.JOBCODE,
A.deptid,
A.HR_STATUS,
A.action,
A.action_reason,
A.SUPERVISOR_ID
   FROM PS_JOB A     WHERE
a.effdt =(select Max(b.effdt) from ps_job b where a.emplid= b.emplid) AND per_org='EMP' and
A.effseq = (select Max(c.effseq) from ps_job c where c.emplid = a.emplid and c.effdt =a.effdt) and
a.empl_rcd =(Select Max(d.empl_rcd) from ps_job d where d.emplid =a.emplid ) and
emplid ='***********'