Prior Row - gtarrant reference

You would want to set up your query using two PS_JOB tbls -I assume your using ADP Enterprise. You would then compare PS_JOB 1 with PS_JOB 2 (PS_JOB 2 being less then PS_JOB 1).

My example below is copied from a report I did in ReportSmith, so the tables are already aliased in the background and this was part of the selection criteria - so you won't be able to cut and paste this in sql plus and have it work. But this will give you the general idea.

You want to set up two statements: 1. the statement below and 2. another statement (using OR) that selects the MIN EFFDT that is equal to the MAX PS_JOB row - this is for anyone in the system that ONLY has one row (ie. New Hires)and doesn't have a prior row which would cause your report to not include these people.

Notice that I concatenated both EFFDT and EFFSEQ and converted that into a number - cuts down on the length of the sql statements to do this seperately.

(SELECT MAX(TO_NUMBER(TO_CHAR(X.EFFDT, 'YYYYMMDD')) || TO_NUMBER(X.EFFSEQ))
FROM PS_JOB X
WHERE (X.EMPLID = JOB1.EMPLID) AND
(TO_NUMBER(TO_CHAR(X.EFFDT, 'YYYYMMDD')) || TO_NUMBER(X.EFFSEQ) < TO_NUMBER(TO_CHAR(JOB2.EFFDT, 'YYYYMMDD')) || TO_NUMBER(JOB2.EFFSEQ)))

Hope this helps you.