Most Recent Title change date

[swagaman]

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.

 

[CharlesCook]

With ADP, SQL Base

This is PCPW yes?

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

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

 

[swagaman]

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

 

[CharlesCook]

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....

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

 

[swagaman]

Got it - that worked. Thanks for your help.

 

[IamnotCharles]

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.

 

[CharlesCook]

There are many ways to skin a cat...

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting