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 ='***********' 

Comments

Post a Comment

Popular posts from this blog

BI Publisher: If Condition with sub-string in rtf template

-
 <?xdofx:if substr(ACAD_PLAN,1,1) = 'C'  then 'Los Angeles City College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'H'  then 'Los Angeles Harbor College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'M'  then 'Los Angeles Mission College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'P'  then 'Pierce College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'S'  then 'Los Angeles Southwest College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'V'  then 'Los Angeles Valley College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'W'  then 'West Los Angeles College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'T'  then 'LA Trade-Technical Colleg' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'E'  then 'East Los Angeles College' end if?>;
Read more