Average number of employees by month 1

[loveyoursite]

CRV10 - I have a turnover report with a date range, and I need it to calculate the average number of active employees for each month within that range and calculate the turnover rate for each month as well. For example January 1 through Jun 30. The average number of employees calculation would be the number of active employees on the first day of the month plus the number of active employees on the last day of the month and divide that by 2. The turnover would be the number of terms for the month divided by the average number of active employees for that month. The report is currently grouped by process level (cost center) then by department name. I'm not sure how to go about this and would appreciate any advice. Should I group differently and using running totals? Here is the SQL:

SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "EMPLOYEE"."DATE_HIRED", "EMPLOYEE"."TERM_DATE", "EMPLOYEE"."PROCESS_LEVEL", "EMPLOYEE"."DEPARTMENT", "EMPLOYEE"."EMP_STATUS", "EMPLOYEE"."JOB_CODE", "VW_TERMINATES_REASONS"."DESCRIPTION", "VW_TERMINATES_REASONS"."CODE", "VW_TERMINATES_REASONS"."FIELD_NAME", "DEPTCODE"."R_NAME"
FROM ("LAWPROD"."EMPLOYEE" "EMPLOYEE" LEFT OUTER JOIN "LAWPROD"."VW_TERMINATES_REASONS" "VW_TERMINATES_REASONS" ON ("EMPLOYEE"."COMPANY"="VW_TERMINATES_REASONS"."COMPANY") AND ("EMPLOYEE"."EMPLOYEE"="VW_TERMINATES_REASONS"."EMPLOYEE")) INNER JOIN "LAWPROD"."DEPTCODE" "DEPTCODE" ON (("EMPLOYEE"."COMPANY"="DEPTCODE"."COMPANY") AND ("EMPLOYEE"."PROCESS_LEVEL"="DEPTCODE"."PROCESS_LEVEL")) AND ("EMPLOYEE"."DEPARTMENT"="DEPTCODE"."DEPARTMENT")
WHERE "EMPLOYEE"."EMP_STATUS"<'34'
ORDER BY "EMPLOYEE"."PROCESS_LEVEL", "DEPTCODE"."R_NAME"

Thanks so much

 

[smcnulty2000]

This is a real bear of a report.

The only way I know how to do it is to start with a beginning employee amount, and then count the number of terms and number of new hires as each month goes by. So some sort of running sum or running total is what you'll need.

Part of the problem with this idea is that you can't group these easily. The problem is that you probably want to group by month but the software will want to know whether you want the term date or the hire date. But it won't take both.

You might have to go to your database and make a union query (or make it in Crystal but that's usually not better) that combines the dates into a single column. A given employee might have two dates, a hire and a term. Some will only have one. Your union query should include a field that tells whether it was a hire or term.

If you have that, your report should be a little easier.

That's my opinion.

scott.