Effective Dated Query

Effective Dated Query:

SELECT * FROM PS_JOB J

WHERE J.EMPLID =  0052

      AND J.EFFDT = (SELECT MAX(J1.EFFDT)

                   FROM PS_JOB J1

                         WHERE J1.EMPLID = J.EMPLID

                               AND J1.EMPL_RCD = J.EMPL_RCD

                               AND J1.EFFDT <= SYSDATE)

     AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)

                      FROM PS_JOB J2

 

                          WHERE J2.EMPLID = J.EMPLID

                               AND J2.EMPL_RCD = J.EMPL_RCD

                               AND J2.EFFDT = J.EFFDT)