Using WHERE TOTAL Selection Tests

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:  

 

Related Topic:  WHERE Screening Statements