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.