round

Overview

The round statement is used to ensure that numeric values in a column are whole multiples of a specified number.

Syntax

roundcolName [direction][to nearestvalue]

Details

The round statement is used to round numbers to the nearest multiple of any integer or floating point number.

The parameters supported by round are as follows:

Parameter	Default	Purpose
colName	n/a	The name of the column containing the values to round
direction	up	Whether to round to the next highest (up ) or lowest ( down ) multiple of value
value	1	A value determining the granularity of the rounding

The simplest form of the statement is round colName. This will use the defaults shown in the table above such that values are rounded to the next highest integer. Alternatively, the statement round colName down will round down to the nearest integer.

If the value argument is provided then the numbers in colName will be rounded up or down to the nearest multiple of value. For example, the statement ...

round Quantity up to nearest 5

... will round the numbers in the column Quantity up to the next multiple of 5. Thus any number in Quantity higher than 10 and less than 15 will be rounded to 15.

When specifying the value argument, floating point values are supported.

Be careful not to confuse the round statement with the ROUND function.

Additional notes

The round statement observes the following rules:

• Non-numeric values in colName are ignored

• Blank values, or a value of 0 in colName are ignored

• Numbers in colName that are already a whole multiple of value will not be modified

The round statement may be used in the body of a where statement to perform conditional rounding.

Examples

round Quantity                       # Round up to nearest integer
round Quantity down                  # Round down to nearest integer
round Quantity up to nearest 4       # Round up to next multiple of 4
round Quantity down to nearest 2     # Round down to next lowest even number

round Quantity up to nearest 0.5     # ie: 2.25 -> 2.5
round Quantity down to nearest 0.1   # ie: 23.34567 -> 23.3
round Quantity down to nearest 0.01  # ie: 23.34567 -> 23.34

where ( ([quantity] > 5) && ([quantity] <= 100) ) {
    round quantity up to nearest 10       # Force consumption in blocks of 10
}

where ([quantity] > 100) {
       round quantity up to nearest 5     # Better deals for larger consumption
}