Sample Request: Years Of Service

The sample report below identifies the years of service as of December 31, 2005 for your active and onleave employees, and includes their current address information. The report is generated using the Active Current Status (AC) file, and uses the field Employment Date (AC:EMPDATE) as the basis for the employee's initial hire date at the campus. Only a portion of the actual report is shown below. The complete report request is available in the CIRS common library as FOC6006.

YEARS AC:WNAME AC:EMPDATE STREET CITY AND STATE ZIP

----- -------- ---------- ------ -------------- ---

5 CROW, HENRY 12/01/2000 123 EAST ST CHICO CA 95928

DEER, SUE, 03/27/2000 123 WEST ST CHICO CA 95928

LION, SARAH 09/13/2000 123 NORTH ST CHICO CA 95928

10 APE, JOHN 07/17/1995 123 SOUTH ST CHICO CA 95928

The AC file does have a field for Employment Years (XX:EMPYRS) based on the Employment Date, but that field is calculated using the date the AC file was created (AC:DAO). Since this report needs December 31 as the calculation date, a defined field (YEARS) was created to determine the total years of service. As written, the request will produce a printed report. To produce a file in a downloadable format, un-comment the next to the last line of the report request before executing.

EX AC

DEFINE FILE AC ADD

YEARS/I5 = ('DEC 31 2005' - AC:EMPDATE)/365;

END

TABLE FILE AC

SUM AC:EMPDATE AC:STREET AC:CITYST AC:ZIP

BY YEARS

BY AC:WNAME

IF YEARS EQ 5 OR 10 OR 15 OR 20 or 25 OR 30 OR 35 OR 40 OR 45 OR 50

-* on table hold as permlrg format lotus

END

Things to note:

Since there are only active and on leave employee positions in the AC file, a screening statement is not needed to select those employees.
The field YEARS is defined as an integer (I5), to display as a whole number. If you would like to see years in fractions, define the field with a packed decimal format (e.g., YEARS/P8.2) and change the screening statement to use ranges. For example, WHERE (YEARS FROM 5.0 TO 5.99) OR (YEARS FROM 10 to 10.99)
The verb SUM is used instead of PRINT to display the employment date and address fields. This is to produce only one row of data for employees with multiple active positions. If additional fields are needed from the position segment, change the verb to PRINT to ensure each occurrence at the position level is displayed.
This report request presumes that Employment Date is the same for all active position sequences held by an employee. If not, the employee may be listed more than once in different sections.

For more information about defines, refer to your FOCUS documentation or call the CIRS Hotline.

Related Topic: Employment Date, Months and Years.