Need Assistance - Review Date rpt with a twist 1

[rougex]

Hi there . . . SQL Servier - ADP Enterprise
I am creating a Next Review Date report for our company scorecard and need to look at historical records.

Tables: PS_PERSONAL_DATA (ALIAS EE), PS_JOB (ALIAS JOB), AND PS_EMPLOYEE_REVIEW (ALIAS REVIEW)

I am looking for the REVIEW_DT and NEXT_REVIEW_DT that were active as of the JOB tables EFFECTIVE DATE (which is were I put in the historical date). If I dont put selection criteria on the review date I get all their records, and if I put the 'SELECT MAX' formula in the selection criteria I obviously am getting their MAXIMUM or current row. Does anyone know how to get the review date information as of the effective date I enter?

Thank you!!

 

[CharlesCook]

I may be missing something.... It sounds like you want to us. "As of Date Asked of User".

Also look at this PPT on Effective dateed records:

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

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rougex]

I have the Effective Date option correct on the Job Table, however no matter what date I put in there the NEXT REVIEW DATE always picks up the MAX, or duplicates depending on the formula (or no formula) that I enter into the selection crieria.

I am thinking it may be because the PS_EMPLOYEE_REVIEW table is not effective dated in the 'normal' way?

 

[CharlesCook]

Do a Selection Critera like this:

Data Field REVIEW.REVIEW_DT is equal to formula

Here is a formula that will get the max review that is less then or equal to the JOB_EFFDT:

(SELECT
MAX("INNERALIAS"."REVIEW_DT")
FROM
PS_EMPLOYEE_REVIEW INNERALIAS
WHERE
(("INNERALIAS"."EMPLID" = "JOB"."EMPLID")
AND
("INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR")
AND
("INNERALIAS"."REVIEW_DT" <= "JOB"."EFFDT")))

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting