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!

Need Assistance - Review Date rpt with a twist 1

Status
Not open for further replies.

rougex

Technical User
Nov 7, 2006
19
US
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!!
 
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?
 
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")))


Specializing in ReportSmith Training and Consulting
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top