×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need Assistance - Review Date rpt with a twist

Need Assistance - Review Date rpt with a twist

Need Assistance - Review Date rpt with a twist

(OP)
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!!

RE: Need Assistance - Review Date rpt with a twist

(OP)
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?

RE: Need Assistance - Review Date rpt with a twist

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close