Here is a rough query that will display all the employees who have a max termed row and are not locked out of the system.
SELECT DISTINCT A.OPRID, A.EMPLID, C.ALTER_EMPLID, A.ACCTLOCK, B.NAME, A.LASTUPDDTTM, A.LASTPSWDCHANGE FROM PSOPRDEFN A, PS_NAMES B, PS_PERS_DATA_EFFDT C, PS_JOB JOBWHERE A.ACCTLOCK <> 1 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND JOB.EMPLID = A.EMPLID AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1 WHERE JOB1.EMPLID = JOB.EMPLID AND JOB1.EFFSEQ = JOB.EFFSEQ AND JOB1.EFFDT <= SYSDATE) AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = JOB.EFFDT) AND JOB.ACTION = 'TER'
1 comment:
One small change needed for this SQL is to removed this piece:
AND JOB1.EFFSEQ = JOB.EFFSEQ
else you will pick up rows where someone has been rehired. So the SQL will look like:
SELECT DISTINCT A.OPRID, A.EMPLID, C.ALTER_EMPLID, A.ACCTLOCK, B.NAME, A.LASTUPDDTTM,
A.LASTPSWDCHANGE
FROM PSOPRDEFN A, PS_NAMES B, PS_PERS_DATA_EFFDT C, PS_JOB JOB
WHERE A.ACCTLOCK <> 1 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID
AND JOB.EMPLID = A.EMPLID
AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1
WHERE JOB1.EMPLID = JOB.EMPLID
AND JOB1.EFFDT <= SYSDATE)
AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2
WHERE JOB2.EMPLID = JOB.EMPLID
AND JOB2.EFFDT = JOB.EFFDT)
AND JOB.ACTION = 'TER'
Post a Comment