The WHERE TOTAL command is very useful when you want to select records based on the aggregate value of a field. Unlike regular WHERE screening statements that evaluate data before it is retrieved, the WHERE TOTAL command screens data after all records are selected.
For example, consider the following report request which identifies the total regular and overtime hours paid to hourly intermittent employees in the current fiscal year:
-* execute Using the PH - Fiscal year file
EX PH
TABLE FILE PH
SUM PH:HOURSPAID/p12.2
BY PH:SSA
BY PH:WNAME
Where ph:rollcode eq '3'
WHERE PH:PAYMETYP EQ '0' OR '1'
END
Report Generated:
PH:SSA PH:WNAME PH:HOURSPAID
------ -------- ------------
123-45-6789 penguin, pb 236.00
234-56-7890 monkey, aj 1551.00
345-67-8901 bear, pj 44.00
By adding the selection test WHERE TOTAL PH:HOURSPAID GE 1000, only those hourly intermittent employees paid a total of 1000 or more regular and overtime hours in the current fiscal year will be selected for the final report.
-* execute Using the PH - Fiscal year file
EX PH
TABLE FILE PH
SUM PH:HOURSPAID/p12.2
BY PH:SSA
BY PH:WNAME
Where ph:rollcode eq '3'
WHERE PH:PAYMETYP EQ '0' OR '1'
WHERE TOTAL PH:HOURSPAID GE 1000
END
Report Generated:
PH:SSA PH:WNAME PH:HOURSPAID
------ -------- ------------
234-56-7890 monkey, aj 1551.00
Items to note:
-
The WHERE TOTAL command must be used after the verb statement.
-
The report requests above use the PH file which contains all payments issued to your employees - including those issued by another campus.
-
To retrieve only your campus data, use the screening statement: WHERE PH:CAMPX EQ X (where X is your alpha campus code).
Related Topic: WHERE Screening Statements