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