Using Loops - HEADCOUNT Report over time

Using Loops - HEADCOUNT Report over time

By Emily Maxwell

Problem:

Create a headcount report that will show the total number of employees employed by month using employees' hire dates and termination dates without having to write code for every month. For example: an employee hired in Feb and termed in April, should count as a 1 in the months of Feb, March, and April.

Solution:

* Calculate the number of months between the two user-defined dates.

* Use one loop to calculate the number of active employees in each month and hold.

* Use another loop to match up all the hold files created from the first loop. 

* Display results.

Code:

-DEFAULT begindate = '01/01/2007';
-DEFAULT enddate   = '04/30/2008';

-* calculate number of months between the begin date  & end date
-SET &beginfmt     = DATECVT(EDIT(&begindate,'99$99$9999'),'A8MDYY','I8');
-SET &endfmt       = DATECVT(EDIT(&enddate,'99$99$9999'),'A8MDYY','I8');
-SET &total_months = DATEDIF(&beginfmt, &endfmt,'M');
-SET &this_month   = 0;

-* loop for each month to calculate number of  employees
-REPEAT ACTIVELOOP FOR &this_month FROM 0 TO &total_months;

-SET &holdfile = 'MONTH'|&this_month;

DEFINE FILE EMPLOYEES
THISMONTH/MYY     = DATEADD(&beginfmt, 'M', &this_month);
HIREMONTH/MYY     = HIREDATE;
TERMMONTH/MYY     = TERMDATE;
ACTIVE/A1         = IF HIREMONTH LE THISMONTH AND ((TERMDATE  IS MISSING) OR
                       (TERMMONTH GE THISMONTH)) THEN 'Y' ELSE 'N';
END

TABLE FILE EMPLOYEES
SUM
CNT.EMPLOYEE_ID
BY THISMONTH
WHERE ACTIVE EQ 'Y'
ON TABLE HOLD AS &holdfile
END

-ACTIVELOOP

-* match up hold files created with previous loop
-* note the first months file is MONTH0, second  month is MONTH1, and so on.

-SET &this_month  = 1;

MATCH FILE MONTH0
BY THISMONTH
BY EMPLOYEE_ID AS EMPLOYEE_COUNT

-REPEAT MATCHLOOP FOR &this_month FROM 1 TO &total_months;

RUN

-SET &holdfile = 'MONTH'|&this_month;

FILE &holdfile
BY THISMONTH
BY EMPLOYEE_ID AS EMPLOYEE_COUNT
AFTER MATCH HOLD AS EECOUNTS OLD-OR-NEW

-MATCHLOOP

END
-RUN

-* display data
TABLE FILE EECOUNTS
HEADING
"Headcount By Month"
"&begindate - &enddate"
" "
SUM
EMPLOYEE_COUNT AS '# Employees'
BY THISMONTH AS 'Month'
ON TABLE PCHOLD FORMAT EXL2K
END

Note:

In the event that a user might enter a date range of one month, you can add logic to skip the MATCHLOOP and hold MONTH0 as EECOUNTS if &total_months equal 0.

Results:

If any questions, please contact Emily at Emily_Maxwell@administaff.com.