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!

Pull only most recent record where Job.Change_Pct <>0

Status
Not open for further replies.

Gymnast14

Technical User
Dec 21, 2006
38
US
Hello everyone,

I'm trying to write a report that pulls an employee's most recent pay change only. The problem is, some of our data entry folks don't always use an action of PAY - I had one example this morning where they used "Promotion" with a reason of "Normal Step Progression".

So, I thought a way around this might be to pull the most recent record where Job.Change_Pct does not equal 0.

However, I can't get this to work. This is my SQL statement so far:

(SELECT
MAX("INNERALIAS"."EFFDT")
FROM
PS_JOB INNERALIAS
WHERE
(("INNERALIAS"."EMPLID" = "PS_JOB"."EMPLID")
AND
("PS_JOB"."CHANGE_PCT" <>0)))

This works when an employee's most recent record has a change percent - but does not pull an employee who's most recent record is only a data change, etc.

Also, in the Selections box, I'm using this formula like this: Data field PS_Job.Effdt is equal to formula "formula above" Is this the correct way to use the SQL statement?
Help please! Thanks!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top