Current and only 1 most Previous 2

[vlfox]

Using Reportsmith for Enterprise, SQL, what would be the best method for reporting the current JobCode and the one most previous to that, without capturing all?

The scenario is: someone that has an Action Reason "JTC" with the Action Date being within my specific date range. I want to know the employee's current JobCode and their JobCode immediately prior to the current one. I don't want their entire history however.

ThanX for all the help ya'll provide.

 

[CharlesCook]

This should get you close:

http://charlescook.com/Examples/Current_JOBCODE_and_only_1_most_Previous_JOBCODE.rpt

Please let me know if you need any Training or Consulting.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[vlfox]

I surely appreciate your help ! The unfortunate part is that I forgot to include that we're SQL so I can't even view the script of your report...I'm sorry.

Is it too long to copy the script here?

ThanX Again !!

 

[CharlesCook]

[!] This should work but I don't have access to a SQL Server system so I can't test it !!![/!]

Do this:

1 Create a NEW report

2 Add the Job table
USE AS OF Current Date for the Effective Date (we will change this latter) !!!
Use EMPLID and EMPL_RCD_NBR for the Effective Date Key

3 Include In Report: EMPLID, EFFDT, ACTION, ACTION_DT, ACTION_REASON, JOBCODE

4 Define a Report Variable called Start_Date of type Date

5 Go back and change the reports Effective Date to As of Date Asked Of User
Here is how you do it if you don't know:

http://charlescook.com/PPS/ADP/Repo...To-Effective-Dated-Tables.pps#275,18,Report’s

Effective Date
We did it this way so we could have a Start Date Report Variable and a End Date Report Variable. The ReportsEffectiveDate Report Variable will be our End Date.

6 Create a Selection Critera for ACTION = JTC

7 Create a Selection Critera for ACTION_DATE Is Between Report Variable Start_Date and Report Variable ReportsEffectiveDate

8 Go to Sorting and Sort by EMPLID

9 [!]Run and Save the report[/!]

10 Create a SQL Derived Field named OLD_JOBCODE

Paste the following into it:

ISNULL(
(SELECT
"OLD_JOBCODE"."JOBCODE"
FROM
"PS_JOB" "OLD_JOBCODE"
WHERE
(("OLD_JOBCODE"."EMPLID" = "JOB"."EMPLID")
AND
("OLD_JOBCODE"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR")
AND
("OLD_JOBCODE"."EFFSEQ"=
(SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" "INNERALIAS"
WHERE "INNERALIAS"."EMPLID" = "OLD_JOBCODE"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "OLD_JOBCODE"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" = "OLD_JOBCODE"."EFFDT")
AND
"OLD_JOBCODE"."EFFDT" =
(SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" "INNERALIAS"
WHERE "INNERALIAS"."EMPLID" = "OLD_JOBCODE"."EMPLID"
AND "INNERALIAS"."EMPL_RCD_NBR" = "OLD_JOBCODE"."EMPL_RCD_NBR"
AND "INNERALIAS"."EFFDT" < "JOB"."EFFDT"
AND "INNERALIAS"."JOBCODE" <> "JOB"."JOBCODE"))))
,'N/A')

11 [!]Run and Save the report[/!]

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[vlfox]

Dear Mr. Cook;

YOU ROCK !!!

ThanX, ThanX, and ThanX !!!