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!

Max EFFDT when pay Action = LOA ?

Status
Not open for further replies.

rougex

Technical User
Nov 7, 2006
19
US
Hi there

I am trying to find the MAX EFFDT from PS_EMPLOYEES (alias EE) where ACTION = LOA. It appears that we have PAY and XFR actions when an employee is on Leave, so pulling the effdt date of the table is not getting me the correct information. Any help to guide me would be appreciated!
 
Convert the PS_EMPLOYEES, Effective Date, Selection Critera to SQL and add this line to it. AND "INNERALIAS"."ACTION" = 'LOA' This will get you the MAX dated LOA action.

Also look at:
("PS_EMPLOYEES"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_EMPLOYEES" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_EMPLOYEES"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_EMPLOYEES"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" = "PS_EMPLOYEES"."EFFDT")
AND
"PS_EMPLOYEES"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_EMPLOYEES" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PS_EMPLOYEES"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "PS_EMPLOYEES"."EMPL_RCD_NBR"
[!]AND "INNERALIAS"."ACTION" = 'LOA'[/!]
AND "INNERALIAS"."EFFDT" <= SYSDATE))

Specializing in ReportSmith Training and Consulting
 
Charles - I was actually able to get the data I was needing by using the statement below in my selection criteria. Thanks for your power point by the way! Mighty Helpful

(SELECT MAX (B.REVIEW_DT) FROM
PS_EMPLOYEE_REVIEW B WHERE
REVIEW.EMPLID=B.EMPLID
AND B.REVIEW_DT <= <<ReportsEffectiveDate, "'"dd"-"mmm"-"yyyy"'">>)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top