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!

Viewing most current record only

Status
Not open for further replies.

pranalli

Technical User
Nov 16, 2007
15
US
Hi there, I am new to this forum but it looks like a great place to network -- So thank you ahead of time for any assistance that can be offered.

I am currently building a report in ADP Enterprise using Reportsmith version 3.10, and I am having an issue with history.

A little background first: I am generating a report to be used for monthly compensation, therefore I need to see ONLY the last pay related action, which would either be an employee's last merit increase or date of hire. I have the report narrowed down to the point where I have all of the information I need, with the exception of the fact that I am getting excess history.

My question is: how to I filter the report to only show me the line of data with the most recent "Action Date." In other words, how do I filter the report to tell it to show me only the max of "Action Date?"

Right now I am getting a line showing when an employee was hired, and then additional lines showing each following Merit increase. I only want to view the most recent Merit increase... if there is no merit increase, then the initial hire.

Anyway, I hope I have offered sufficient information for your help. I noticed a lot of requests here ask for the SQL to be pasted, so I will do that:

SELECT DISTINCT
"PS_JOB"."ACTION", "PS_JOB"."ACTION_DT", "PS_JOB"."ACTION_REASON", "PS_JOB"."ANNUAL_RT", "PS_JOB"."CHANGE_AMT", "PS_JOB"."CHANGE_PCT", "PS_JOB"."EMPL_STATUS", "PS_JOB"."EMPLID", "PS_JOB"."DEPTID", "PS_JOB"."LOCATION", "PS_JOB"."HOME_JOBCOST_NBR", "PS_JOB"."JOBCODE", "PS_JOBCODE_TBL"."DESCR", "Get_Supervisor"."NAME", "PS_EMPLOYMENT"."HIRE_DT", "PS_EMPLOYEE_REVIEW"."NEXT_REVIEW_DT", "PS_EMPLOYEE_REVIEW"."REVIEW_DT", "PS_EMPLOYEE_REVIEW"."REVIEW_RATING", "PS_PERSONAL_DATA"."NAME"
FROM
"PS_JOB", "PS_JOBCODE_TBL", "PS_PERSONAL_DATA" "Get_Supervisor", "PS_EMPLOYMENT", "PS_EMPLOYEE_REVIEW", "PS_PERSONAL_DATA"
WHERE
((("PS_JOB"."EMPL_STATUS" = 'A') AND
(("PS_JOBCODE_TBL"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOBCODE_TBL" INNERALIAS
WHERE "INNERALIAS"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
("PS_EMPLOYEE_REVIEW"."NEXT_REVIEW_DT" = TO_DATE('2007-12-01', 'YYYY-MM-DD')) AND
("PS_JOB"."ACTION" IN( 'PAY', 'HIR'))))
AND
("Get_Supervisor"."EMPLID" = "PS_EMPLOYMENT"."SUPERVISOR_ID" ) AND ("PS_JOB"."EMPLID" = "PS_EMPLOYEE_REVIEW"."EMPLID" ) AND ("PS_JOB"."EMPLID" = "PS_EMPLOYMENT"."EMPLID" ) AND ("PS_JOB"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE" ) AND ("PS_JOB"."EMPLID" = "PS_PERSONAL_DATA"."EMPLID" )
ORDER BY
"PS_PERSONAL_DATA"."NAME", "PS_JOB"."ACTION_DT"


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top