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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Current and only 1 most Previous 2

Status
Not open for further replies.

vlfox

Technical User
Oct 13, 2005
40
US
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.
 
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 !!
 
[!] 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:
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[/!]

Specializing in ReportSmith Training and Consulting
 
Dear Mr. Cook;

YOU ROCK !!!

ThanX, ThanX, and ThanX !!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top