Max EFFDT when pay Action = LOA ?

[rougex]

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!

 

[CharlesCook]

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:

http://charlescook.com/PPS/ADP/ReportSmith/Enterprise/Introduction-To-Effective-Dated-Tables.pps

("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))

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[CharlesCook]

I updated this power point with this Technique.....

http://charlescook.com/PPS/ADP/ReportSmith/Enterprise/Introduction-To-Effective-Dated-Tables.pps

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rougex]

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"'">>)

 

[CharlesCook]

You’re welcome. Glad you got it working...

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting