×
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

Most Recent Title change date

Most Recent Title change date

Most Recent Title change date

(OP)
With ADP, SQL Base,

I need to write a report that lists employees and the effective date for their job title.  This would be the most recent changedate in the V-HRP_HSTJOBTITLE table.  I can get all of the records, but I can't figure out how to get the record with the most recent changedate for each employee.

Any help would be most appreciated.

RE: Most Recent Title change date

Quote:

With ADP, SQL Base

This is PCPW yes?

You need to do a Correlated Subquery in your Selection Criteria.  

Quote (www.databasejournal.com):


A correlated subquery is a SELECT statement nested inside another SELECT statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.


In your Selection Criteria you will set up a criteria like this(ish)
Data Field "V-HRP_HSTJOBTITLE"."CHANGEDATE" is equal to formula .....

The formula will be the subquery and will go something like this(I don't have access to PCPW so you will need to fix this)...

(SELECT
     MAX("INNERALIAS"."CHANGEDATE")
FROM
     V-HRP_HSTJOBTITLE INNERALIAS
WHERE
     ("INNERALIAS"."COMPANYCODE" = "V-HRP_HSTJOBTITLE"."COMPANYCODE")
     AND
     ("INNERALIAS"."FILENUMBER" = "V-HRP_HSTJOBTITLE"."FILENUMBER"))

This (with a few minor syntax fixes) will get you the max dated changedate record for each person.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Most Recent Title change date

(OP)
Thanks for the quick reply.  Yes, this is PCPW.  I tried the code that you suggested and must be missing something.  I am getting an error message "command not ended properly".  I have pasted the SQL below.  Can you tell me what I did wrong?

Thanks,

SELECT
REPORTS.V_HRP_HSTJOBTITLE.CHANGEDATE, REPORTS.V_HRP_HSTJOBTITLE.COMPANYCODE, REPORTS.V_HRP_HSTJOBTITLE.FILE#, REPORTS.V_HRP_HSTJOBTITLE.JOBTITLEDESCR, REPORTS.V_EMPLOYEE.HIREDATE, REPORTS.V_EMPLOYEE.LOCATIONCODE, REPORTS.V_EMPLOYEE.NAME, PCPAYSYS.T_CO_LOCATION.LOC_C, PCPAYSYS.T_CO_LOCATION.CO_C, PCPAYSYS.T_CO_LOCATION.DESCRIPTION_TX, PCPAYSYS.T_CO_LOCATION.ALT_DESCRIPTION_TX
FROM
REPORTS.V_HRP_HSTJOBTITLE, REPORTS.V_EMPLOYEE, PCPAYSYS.T_CO_LOCATION
WHERE
(((@Decode(REPORTS.V_EMPLOYEE.LOCATIONCODE,NULL,'*',REPORTS.V_EMPLOYEE.LOCATIONCODE) = PCPAYSYS.T_CO_LOCATION.LOC_C) AND
(@Decode(REPORTS.V_EMPLOYEE.LOCATIONCODE,NULL,'***',REPORTS.V_EMPLOYEE.COMPANYCODE) = PCPAYSYS.T_CO_LOCATION.CO_C) AND
(REPORTS.V_EMPLOYEE.STATUS IN( 'A', 'L')) AND
(REPORTS.V_HRP_HSTJOBTITLE.COMPANYCODE <> '2BP')))
AND
(REPORTS.V_EMPLOYEE.COMPANYCODE = REPORTS.V_HRP_HSTJOBTITLE.COMPANYCODE ) AND (REPORTS.V_EMPLOYEE.FILE# = REPORTS.V_HRP_HSTJOBTITLE.FILE# )
(SELECT
  MAX(INNERALIAS.CHANGEDATE)
FROM
   REPORTS.V_HRP_HSTJOBTITLE INNERALIAS
WHERE
   INNERALIAS.COMPANYCODE = REPORTS.V_HRP_HSTJOBTITLE.COMPANYCODE
   AND
   INNERALIAS.FILENUMBER = REPORTS.V_HRP_HSTJOBTITLE.FILENUMBER))
ORDER BY
REPORTS.V_EMPLOYEE.COMPANYCODE, REPORTS.V_EMPLOYEE.LOCATIONCODE

RE: Most Recent Title change date

Did you create this report by editing the SQL????  If so create a test report without editing the SQL.

You need to fix the FILENUMBER part of your subquery.  I think it should be FILE#.

Also you are missing the....

Quote:

In your Selection Criteria you will set up a criteria like this(ish)
Data Field "V-HRP_HSTJOBTITLE"."CHANGEDATE" is equal to formula .....

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Most Recent Title change date

(OP)
Got it - that worked.  Thanks for your help.

RE: Most Recent Title change date

Another way to do this would be to sort the report alphabetically, then by change date.  Create a group and footer by name.  Insert name, job title and change date in the footer.  It will display the latest change date information.  Then, you can hide the detail by clicking on format, section, hide detail.

RE: Most Recent Title change date

There are many ways to skin a cat...

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