Turnover Rate By Position 1

[MikeBottema]

I am looking for help on a turnover rate report. Similiar to the one in Enterprise V4 (PER010) but I need to show turnover by position and not by department.

Below is the forluma used to calculate the Turnover Rate Report (PER101) in EV4.

Turnover Rate[x] = Average # of employees between two dates (Begin Count [d] + End Count [e]/2) divided by (Terminations[a] + Retirements + Transfer Out[c])

move &BeginCountC to #BeginPlusEnd
add #EndCountC to #BeginPlusEnd
if #BeginPlusEnd > 0
! Separations include terminations and retirements
move #TermsC to #Net_Turnover
add &RetireCountC to #Net_Turnover
multiply 200 times #Net_Turnover
divide #BeginPlusEnd into #Net_Turnover
move #Net_Turnover to $Write_Net_Turnover
print #Net_Turnover (0,{col_turnover_cp}) edit 9999.9
print '%' ()

 

[ArtieArt]

I would create a new RS report using the Job / Jobcode_tbl and just include the Jobcode in the report. Employment table for your term date as well as a selection for it being 2 dates. Derived fields for Terms and one for active and one to get the turnover percent.
Sum(Decode Job Status 'A','1',0)
the same for your terms. This will sum the numbers by Jobcode.
I am running out the door for a plane. Call and I can walk you thru. It's much simpler for me....
678-772-3058c

Artie Dunnings
ADTECH Consulting Inc
HRIS Functional & Technical Trainer
Alpharetta, GA

 

[CharlesCook]

I think this report would be close:

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

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[CharlesCook]

How did this work for you?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[MikeBottema]

Charles,
Your report got us on the right track, but we had to modify it. I have pasted here the sql script of what was created that "alomost" meets our needs. The hard part now is getting it to recognize location and group by location.
Are there any thoughts out there?
Mike

SELECT
NVL((SELECT
COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0), NVL((SELECT
COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0), (((NVL((SELECT COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0)))+((NVL((SELECT COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0))))/2, NVL((SELECT
COUNT("TJOB"."EMPLID")
FROM
"PS_JOB" "TJOB"
WHERE
((("TJOB"."EFFDT" BETWEEN <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("TJOB"."ACTION" = 'TER') AND
("TJOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"TJOB"."JOBCODE"),0), NVL( (SELECT
COUNT("RJOB"."EMPLID")
FROM
"PS_JOB" "RJOB"
WHERE
((("RJOB"."EFFDT" BETWEEN <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("RJOB"."ACTION" = 'RET') AND
("RJOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"RJOB"."JOBCODE"),0), NVL((SELECT
COUNT("PREV"."EMPLID")
FROM
"PS_PERSONAL_DATA" "Personal", "PS_JOB" "CURR", "PS_JOB" "PREV", "PS_DEPT_TBL", "PS_JOBCODE_TBL", "PS_JOBCODE_TBL" "PS_JOBCODE_TB2", "XLATTABLE", "PS_LOCATION_TBL", "XLATTABLE" "XLATTABLE2", "XLATTABLE" "XLATTABLE3", "PS_JOBCODE_TBL" "PS_JOBCODE_T3", "PS_DEPT_TBL" "PS_DEPT_TB2", "XLATTABLE" "XLATTABLE4", "XLATTABLE" "XLATTABLE5"
WHERE
((("CURR"."EFFDT" = (SELECT MAX(JOB.EFFDT)
FROM PS_JOB JOB
WHERE CURR.EMPLID = JOB.EMPLID
AND JOB.ACTION = 'POS'
AND JOB.EFFDT BETWEEN
<<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>
AND
<<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>)) AND
(TO_CHAR("PREV"."EFFDT",'YYYYMMDD')||"PREV"."EFFSEQ" = (
SELECT MAX(TO_CHAR(B.EFFDT,'YYYYMMDD')||B.EFFSEQ)
FROM PS_JOB B
WHERE B.EMPLID = "CURR"."EMPLID"
AND TO_CHAR(B.EFFDT,'YYYYMMDD')||B.EFFSEQ <
TO_CHAR("CURR"."EFFDT",'YYYYMMDD')||"CURR"."EFFSEQ")) AND
("XLATTABLE"."FIELDNAME" = 'ACTION') AND
("XLATTABLE"."EFF_STATUS" = 'A') AND
("XLATTABLE"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE2"."FIELDNAME" = 'REG_TEMP') AND
("XLATTABLE2"."EFF_STATUS" = 'A') AND
("XLATTABLE2"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE3"."FIELDNAME" = 'FULL_PART_TIME') AND
("XLATTABLE3"."EFF_STATUS" = 'A') AND
("XLATTABLE3"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE4"."FIELDNAME" = 'REG_TEMP') AND
("XLATTABLE4"."EFF_STATUS" = 'A') AND
("XLATTABLE4"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE5"."FIELDNAME" = 'FULL_PART_TIME') AND
("XLATTABLE5"."EFF_STATUS" = 'A') AND
("XLATTABLE5"."LANGUAGE_CD" = 'ENG') AND
("PREV"."JOBCODE" = "JD"."JOBCODE")))
AND
("Personal"."EMPLID" = "CURR"."EMPLID" ) AND ("Personal"."EMPLID" = "PREV"."EMPLID" ) AND ("CURR"."DEPTID" = "PS_DEPT_TBL"."DEPTID" ) AND ("CURR"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE" ) AND ("CURR"."JOBCODE" = "PS_JOBCODE_TB2"."JOBCODE" ) AND ("CURR"."ACTION" = "XLATTABLE"."FIELDVALUE" ) AND ("CURR"."LOCATION" = "PS_LOCATION_TBL"."LOCATION" ) AND ("CURR"."REG_TEMP" = "XLATTABLE2"."FIELDVALUE" ) AND ("CURR"."FULL_PART_TIME" = "XLATTABLE3"."FIELDVALUE" ) AND ("PREV"."JOBCODE" = "PS_JOBCODE_T3"."JOBCODE" ) AND ("PREV"."DEPTID" = "PS_DEPT_TB2"."DEPTID" ) AND ("PREV"."REG_TEMP" = "XLATTABLE4"."FIELDVALUE" ) AND ("PREV"."FULL_PART_TIME" = "XLATTABLE5"."FIELDVALUE" )
GROUP BY
"PREV"."JOBCODE"),0), CASE WHEN
((((NVL((SELECT COUNT("JOB"."EMPLID") FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0)))+((NVL((SELECT COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0))))/2)=0
THEN 0
ELSE
(((NVL((SELECT COUNT("TJOB"."EMPLID")
FROM
"PS_JOB" "TJOB"
WHERE
((("TJOB"."EFFDT" BETWEEN <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("TJOB"."ACTION" = 'TER') AND
("TJOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"TJOB"."JOBCODE"),0)))+((NVL( (SELECT COUNT("RJOB"."EMPLID")
FROM
"PS_JOB" "RJOB"
WHERE
((("RJOB"."EFFDT" BETWEEN <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("RJOB"."ACTION" = 'RET') AND
("RJOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"RJOB"."JOBCODE"),0)))+((NVL((SELECT COUNT("PREV"."EMPLID")
FROM
"PS_PERSONAL_DATA" "Personal", "PS_JOB" "CURR", "PS_JOB" "PREV", "PS_DEPT_TBL", "PS_JOBCODE_TBL", "PS_JOBCODE_TBL" "PS_JOBCODE_TB2", "XLATTABLE", "PS_LOCATION_TBL", "XLATTABLE" "XLATTABLE2", "XLATTABLE" "XLATTABLE3", "PS_JOBCODE_TBL" "PS_JOBCODE_T3", "PS_DEPT_TBL" "PS_DEPT_TB2", "XLATTABLE" "XLATTABLE4", "XLATTABLE" "XLATTABLE5"
WHERE
((("CURR"."EFFDT" = (SELECT MAX(JOB.EFFDT)
FROM PS_JOB JOB
WHERE CURR.EMPLID = JOB.EMPLID
AND JOB.ACTION = 'POS'
AND JOB.EFFDT BETWEEN
<<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>
AND
<<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>)) AND
(TO_CHAR("PREV"."EFFDT",'YYYYMMDD')||"PREV"."EFFSEQ" = (
SELECT MAX(TO_CHAR(B.EFFDT,'YYYYMMDD')||B.EFFSEQ)
FROM PS_JOB B
WHERE B.EMPLID = "CURR"."EMPLID"
AND TO_CHAR(B.EFFDT,'YYYYMMDD')||B.EFFSEQ <
TO_CHAR("CURR"."EFFDT",'YYYYMMDD')||"CURR"."EFFSEQ")) AND
("XLATTABLE"."FIELDNAME" = 'ACTION') AND
("XLATTABLE"."EFF_STATUS" = 'A') AND
("XLATTABLE"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE2"."FIELDNAME" = 'REG_TEMP') AND
("XLATTABLE2"."EFF_STATUS" = 'A') AND
("XLATTABLE2"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE3"."FIELDNAME" = 'FULL_PART_TIME') AND
("XLATTABLE3"."EFF_STATUS" = 'A') AND
("XLATTABLE3"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE4"."FIELDNAME" = 'REG_TEMP') AND
("XLATTABLE4"."EFF_STATUS" = 'A') AND
("XLATTABLE4"."LANGUAGE_CD" = 'ENG') AND
("XLATTABLE5"."FIELDNAME" = 'FULL_PART_TIME') AND
("XLATTABLE5"."EFF_STATUS" = 'A') AND
("XLATTABLE5"."LANGUAGE_CD" = 'ENG') AND
("PREV"."JOBCODE" = "JD"."JOBCODE")))
AND
("Personal"."EMPLID" = "CURR"."EMPLID" ) AND ("Personal"."EMPLID" = "PREV"."EMPLID" ) AND ("CURR"."DEPTID" = "PS_DEPT_TBL"."DEPTID" ) AND ("CURR"."JOBCODE" = "PS_JOBCODE_TBL"."JOBCODE" ) AND ("CURR"."JOBCODE" = "PS_JOBCODE_TB2"."JOBCODE" ) AND ("CURR"."ACTION" = "XLATTABLE"."FIELDVALUE" ) AND ("CURR"."LOCATION" = "PS_LOCATION_TBL"."LOCATION" ) AND ("CURR"."REG_TEMP" = "XLATTABLE2"."FIELDVALUE" ) AND ("CURR"."FULL_PART_TIME" = "XLATTABLE3"."FIELDVALUE" ) AND ("PREV"."JOBCODE" = "PS_JOBCODE_T3"."JOBCODE" ) AND ("PREV"."DEPTID" = "PS_DEPT_TB2"."DEPTID" ) AND ("PREV"."REG_TEMP" = "XLATTABLE4"."FIELDVALUE" ) AND ("PREV"."FULL_PART_TIME" = "XLATTABLE5"."FIELDVALUE" )
GROUP BY
"PREV"."JOBCODE"),0))))/(((((NVL((SELECT COUNT("JOB"."EMPLID") FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0)))+((NVL((SELECT COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<EndDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0))))/2))
END,
"JD"."DESCR", "JD"."JOBCODE"
FROM
"PS_JOBCODE_TBL" "JD"
WHERE
(((("JD"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOBCODE_TBL" INNERALIAS
WHERE "INNERALIAS"."JOBCODE" = "JD"."JOBCODE"
AND "INNERALIAS"."EFFDT" <= SYSDATE))) AND
((NVL((SELECT
COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0)) <> '0') AND
("JD"."JOBCODE" <> '999999') AND
(("JD"."JOBCODE" LIKE '___9__') OR
("JD"."JOBCODE" LIKE '___6__') OR
("JD"."JOBCODE" LIKE '___7__'))))
ORDER BY
NVL((SELECT
COUNT("JOB"."EMPLID")
FROM
"PS_JOB" "JOB"
WHERE
(((("JOB"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" = "JOB"."EFFDT")
AND
"JOB"."EFFDT" = (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "PS_JOB" INNERALIAS
WHERE "INNERALIAS"."EMPL_RCD_NBR" = "JOB"."EMPL_RCD_NBR"
AND "INNERALIAS"."EMPLID" = "JOB"."EMPLID"
AND "INNERALIAS"."EFFDT" <= <<BegDate, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>))) AND
("JOB"."EMPL_STATUS" IN( 'A', 'L', 'S')) AND
("JOB"."JOBCODE" = "JD"."JOBCODE")))
GROUP BY
"JOB"."JOBCODE"),0) DESC, "JD"."DESCR