Count of Codes per Employee ID 1

[LifespanWriter]

I'm working on a report to show any employees in a given time period who have at least 2 earning codes (ERNCD) listed in Additional Pay Data in that period. I currently have it showing all employees with addition pays in that period, and have it grouped by employee ID to show the count of ERNCD's for each employee, but I can't seem to get it to only show those employees with >1 ERNCD's... any suggestions? This is what the SQL looks like:

SELECT DISTINCT
"C"."COMPANY", "C"."PAYGROUP", "SYSADM"."PS_PERSONAL_DATA"."NAME", "F"."EMPLID", "F"."ERNCD", "F"."HOURLY_RT", "F"."OTH_HRS", "F"."OTH_PAY", "F"."EARNINGS_END_DT", "F"."EFFDT", "F"."ADDL_SEQ", "F"."DED_TAKEN", "F"."SEPCHK", "F"."OK_TO_PAY", "F"."GOAL_AMT"
FROM
"SYSADM"."PS_JOB" "C", "SYSADM"."PS_PERSONAL_DATA", "SYSADM"."PS_ADDL_PAY_DATA" "F"
WHERE
((("F"."EFFDT" BETWEEN <<DATE1, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<DATE2, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("F"."ERNCD" <> 'FL') AND
("C"."EFFDT" = (Select max(B.EFFDT)from sysadm.ps_job B where C.EMPLID=B.EMPLID))))
AND
("C"."EMPLID" = "SYSADM"."PS_PERSONAL_DATA"."EMPLID" ) AND ("C"."EMPLID" = "F"."EMPLID" )
ORDER BY
"C"."COMPANY", "SYSADM"."PS_PERSONAL_DATA"."NAME"



Richard Sanders
Payroll/HR Systems Specialist
Lifespan Corporate Services
Providence, RI

 

[ReportSmithing]

Just add the statement to your WHERE clause

...where F.ERNCD > '1'...

Of course this will pull all alpha-numeric data since alpha chars come after numbers in the order schema - are you trying to say ...where F.ERNCD IS NOT NULL..? OR ...where F.ERNCD > ' '?

Also, make sure you also MAX your EFFSEQ when using PS_JOB.

 

[LifespanWriter]

I'm trying to just limit the selection results by only showing where the "count" of ERNCD's for each employee ID is greater than 2, not where the ERNCD itself is greater than 2.

Is there a way I can even just add a selection criteria that will look in the group summaries/footers rather than in the details section? This would help if it's possible.

Right now, the report shows all employees with ERNCD's in Additional Pay, and I have it grouped (with footers after each) at each employee ID. I currently have the count of each ERNCD showing in the footers to make it easier to determine which employees have more than one ERNCD listed... but since most employees have one ERNCD listed, the resulting report is over 75 pages long. That's why I'd like to find a way to limit it to those employees with 2 or more ERNCD's, which would cut the report down to 2-3 pages.


Richard Sanders
Payroll/HR Systems Specialist
Lifespan Corporate Services
Providence, RI

 

[ReportSmithing]

Cut and paste this into your SQL - you can change the multiple employee count value below...

SELECT DISTINCT
"C"."COMPANY", "C"."PAYGROUP", "SYSADM"."PS_PERSONAL_DATA"."NAME", "F"."EMPLID", "F"."ERNCD", "F"."HOURLY_RT", "F"."OTH_HRS", "F"."OTH_PAY", "F"."EARNINGS_END_DT", "F"."EFFDT", "F"."ADDL_SEQ", "F"."DED_TAKEN", "F"."SEPCHK", "F"."OK_TO_PAY", "F"."GOAL_AMT"
FROM
"SYSADM"."PS_JOB" "C", "SYSADM"."PS_PERSONAL_DATA", "SYSADM"."PS_ADDL_PAY_DATA" "F"
WHERE
((("F"."EFFDT" BETWEEN <<DATE1, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<DATE2, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) AND
("F"."ERNCD" <> 'FL') AND
("C"."EFFDT" = (Select max(B.EFFDT)from sysadm.ps_job B where C.EMPLID=B.EMPLID))))
AND
("C"."EMPLID" = "SYSADM"."PS_PERSONAL_DATA"."EMPLID" ) AND ("C"."EMPLID" = "F"."EMPLID" )
AND (SELECT COUNT("X"."EMPLID") FROM PS_ADDL_PAY_DATA X
WHERE "X"."EMPLID" = "F"."EMPLID" AND
"X"."EFFDT" BETWEEN <<DATE1, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">> AND <<DATE2, "TO_DATE('"yyyy"-"mm"-"dd"', 'YYYY-MM-DD')">>) > 1
ORDER BY
"SYSADM"."PS_PERSONAL_DATA"."NAME

 

[LifespanWriter]

It works! I had previously tried adding something similar to this:

(SELECT COUNT("X"."EMPLID") FROM PS_ADDL_PAY_DATA X
WHERE "X"."EMPLID" = "F"."EMPLID"

But it appears that my problem was that I was trying to use ERNCD instead of EMPLID, and I also didn't try using the date selection along with it. You really know your stuff--thanks so much for your help!

~Rich


Richard Sanders
Payroll/HR Systems Specialist
Lifespan Corporate Services
Providence, RI