Prefix operators are useful commands that perform calculations directly on the aggregate (SUM or COUNT) value of a specified field. They are applied to a single fieldname and affect only that field. In many cases they can eliminate the need for a define and/or a hold file. This article will highlight four of those operators, MIN., AVE., PCT., and DST.
The prefix operator: MIN.
The prefix operator, MIN. will identify the minimum values within a sort group. If the request does not include a sort phrase, then it will identify the minimum values for the entire report. In the example below, MIN. is used to identify the earliest effective date (TR:EFFDATE) an employee was appointed to a lecturer classification at your campus. The report uses the Transaction Data file which has active and separated positions for employees, so the field Employee Status (TR:STATUS) is used to evaluate only position sequences for employees whose overall status is active.
Report Request | Report Generated |
EX TR TABLE FILE TR SUM MIN.TR:EFFDATE BY TR:SSA IF TR:STATUS EQ 1 IF TR:CLASS EQ 2358 END | MIN TR:SSA TR:EFFDATE ------ ---------- 011-11-1111 01/29/2004 022-22-2222 08/22/1989 033-33-3333 08/21/2002 |
The prefix operator: AVE.
The prefix operator, AVE. computes the average value for the specified field. If your report request does not include a sort phrase, it calculates the average for the entire report. Otherwise, the average is computed as the sum of the values within a sort group divided by the number of records in that sort group. In the example below, AVE. is used to determine the average systemwide base pay, by class code, for active MPP employees.
Report Request | Report Generated |
EX SAC TABLE FILE SAC SUM AVE.SAC:BASEPAY BY SAC:CLASS IF SAC:CBID EQ M80 END | AVE SAC:CLASS SAC:BASEPAY --------- ----------- 3300 $12,670.18 3306 $9,111.35 3312 $6,347.01 3318 $4,553.00 |
The prefix operator: PCT.
The prefix operator, PCT. calculates the percentage that a row (sort group) makes up of the column’s total value. In the example below, PCT. it is used to determine the percent of active and onleave full-time equivalent positions, by ethnic group, at your campus. Note: The ethnic group value of '---' indicates an invalid ethnic code value on the employee's record.
Report Request | Output Generated |
EX AC TABLE FILE AC SUM PCT.AC:FTE BY AC:ETHNICGRP END | PCT AC:ETHNICGRP AC:FTE ----------- ------ ---- .139 AMI .362 ASN 9.951 BLK 4.776 HSP 2.208 OTH 1.726 WHT 80.834 |
The prefix operator: DST.
The prefix operator, DST. allows you to determine the total number of distinct values for a specific field in a single pass of the database. In the example below, DST. is used to identify the distinct number of employees paid by your campus, for the last five tax years. An employee will be counted only once regardless of how many payments were issued during the specified timeframe.
Report Request | Output Generated |
EX PHS TABLE FILE PHS COUNT DST.PHS:SSA BY PHS:TAXYEAR IF PHS:TAXYEAR FROM 2001 TO 2005 END | DST PHS:TAXYEAR PHS:SSA ----------- ------- 2001 4,034 2002 4,135 2003 4,297 2004 4,303 2005 4,456
|
Last Updated: February 27, 2024