Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Prior Row - gtarrant reference

Status
Not open for further replies.

cabbey77

Technical User
Aug 6, 2004
68
US
Hello gtarrant,

How exactly do you go about comparing something with the previous row? (see below)

Thanks,

Christine

gtarrant (TechnicalUser) May 6, 2004
If you're working with ADP Enterprise, there is a field on PS_JOB named CHANGE_AMT. Any change to this field is an indication of a rate change. Or you can just compare PS_JOB with PS_JOB (prior row) to read any changes.



 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top