Re: Future Effective Dated rows

[George Jansen <GJANSEN@AFLCIO.ORG>]

What about using a NOT EXISTS subquery?  My reasoning is that the
subquery below will for a given emplid and empl_rcd# exclude:

a. current records with a newer current record
b. future records with an older future record.
c. future records with a current record.
d. records having another matching of date but with a higher sequence.

By moving the check on EFFSEQ into the same subquery we can eliminate
an extra index scan of PS_JOB.

I had to comment out the lines relating to PS_DISABILITY and
PS_PERS_NID, since my system doesn't have them, and I changed $AsOfDate
to Sysdate since this was SQL*Plus, not SQR. The code is as follows:

SELECT
A.EMPLID,
B.EMPL_RCD#,
C.EFFDT
FROM  PS_PERSONAL_DATA A,
      PS_EMPLOYMENT    B,
      PS_JOB           C
/*,
      PS_DISABILITY     PSD ,
      PS_PERS_NID NID
*/
WHERE A.PER_STATUS = 'E'
/*
  AND PSD.EMPLID=A.EMPLID
  AND NID.EMPLID = A.EMPLID
*/
  AND B.EMPLID     = A.EMPLID
  AND C.EMPLID     = B.EMPLID
  AND C.EMPL_RCD#  = B.EMPL_RCD#
  AND NOT EXISTS
      (SELECT 1
       FROM PS_JOB H
       WHERE H.EMPLID = C.EMPLID
         AND H.EMPL_RCD# = C.EMPL_RCD#
         AND (   (Sysdate >= H.EFFDT
                  AND H.EFFDT > C.EFFDT) -- H has current row newer, C
current
              OR (C.EFFDT > H.EFFDT
                  AND H.EFFDT > Sysdate) -- H has future row older, C
future
              OR (C.EFFDT > Sysdate
                  AND Sysdate >= H.EFFDT) -- H is current, C future
              OR (H.EFFDT = C.EFFDT
                  AND H.EFFSEQ > C.EFFSEQ)));