loveyoursite
Technical User
CRV10. My report uses 2 tables: EMPLOYEE and JOBCODE. Purpose of report is to show average pay rate for each job code based upon the number of employees in that job. There is one group that is jobcode.
I have a left outer join from the JOBCODE table to the EMPLOYEE table. The joined fields are company and jobcode.
In the group footer is a sum field counting the nbr of employees in each job and a sum field averaging payrate.
Problem: Any job code that currently has no employees, does not show up on the report. I need ALL job codes to show up on the report even if no employees currently have that job. If no employees have the job, then the nbr of employees in that job count should be 0 and the average pay rate should be 0.00.
Here is the SQL:
SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."PAY_RATE", "EMPLOYEE"."JOB_CODE", "JOBCODE"."DESCRIPTION"
FROM "LAWPROD"."JOBCODE" "JOBCODE" LEFT OUTER JOIN "LAWPROD"."EMPLOYEE" "EMPLOYEE" ON ("JOBCODE"."COMPANY"="EMPLOYEE"."COMPANY") AND ("JOBCODE"."JOB_CODE"="EMPLOYEE"."JOB_CODE")
WHERE NOT ("EMPLOYEE"."EMP_STATUS"='30' OR "EMPLOYEE"."EMP_STATUS"='31' OR "EMPLOYEE"."EMP_STATUS"='32' OR "EMPLOYEE"."EMP_STATUS"='33' OR "EMPLOYEE"."EMP_STATUS"='34')
ORDER BY "EMPLOYEE"."JOB_CODE"
Expected Result:
Job Code 0132 17 emps in job 12.34 Avg Pay rate
Job Code 0197 0 emps in job 0.00 Avg Pay Rate
Any help would be much appreciated.
I have a left outer join from the JOBCODE table to the EMPLOYEE table. The joined fields are company and jobcode.
In the group footer is a sum field counting the nbr of employees in each job and a sum field averaging payrate.
Problem: Any job code that currently has no employees, does not show up on the report. I need ALL job codes to show up on the report even if no employees currently have that job. If no employees have the job, then the nbr of employees in that job count should be 0 and the average pay rate should be 0.00.
Here is the SQL:
SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."PAY_RATE", "EMPLOYEE"."JOB_CODE", "JOBCODE"."DESCRIPTION"
FROM "LAWPROD"."JOBCODE" "JOBCODE" LEFT OUTER JOIN "LAWPROD"."EMPLOYEE" "EMPLOYEE" ON ("JOBCODE"."COMPANY"="EMPLOYEE"."COMPANY") AND ("JOBCODE"."JOB_CODE"="EMPLOYEE"."JOB_CODE")
WHERE NOT ("EMPLOYEE"."EMP_STATUS"='30' OR "EMPLOYEE"."EMP_STATUS"='31' OR "EMPLOYEE"."EMP_STATUS"='32' OR "EMPLOYEE"."EMP_STATUS"='33' OR "EMPLOYEE"."EMP_STATUS"='34')
ORDER BY "EMPLOYEE"."JOB_CODE"
Expected Result:
Job Code 0132 17 emps in job 12.34 Avg Pay rate
Job Code 0197 0 emps in job 0.00 Avg Pay Rate
Any help would be much appreciated.