Presents your 
SQL SERVER E-NEWSLETTER for November 19, 2002

<------------------------------------------->

Round numbers using the CEILING or FLOOR function

The mathematical CEILING function will return an integer that is equal
to or greater than the argument given. Similar to CEILING is the FLOOR
mathematical function, which will return an integer that is equal to or less
than the argument given. Both will return only whole numbers, not fractions.

Rounding numbers is a good use for the CEILING or FLOOR function. If you
find a lot of calculations using rounding in your database for the same
input columns, you may be able to speed up database performance by
calculating these numbers and storing them in the database. Columns would be
added to the tables, and the columns would be calculated in either batch
DML or through triggers. Depending upon the application's business rules
for rounding, you could use the CEILING function and/or FLOOR function in
rounding numbers.

Following is a sample script that illustrates rounding up with the
CEILING function and rounding down with the FLOOR function. For this example,
if there's any number in that fourth decimal point, the CEILING function
will round up, whereas the FLOOR function will truncate numbers to the
right of the decimal point. The expected output is commented at the bottom
of the script for verification.

SET NOCOUNT ON
GO
DECLARE @MyNum DECIMAL(10,4), @MyNumAdd DECIMAL(10,4),
@MyNumMinus DECIMAL(10,4)

SET @MyNum = 265.1
SET @MyNumAdd = @MyNum + .5
SET @MyNumMinus = @MyNum - .4
SELECT @MyNum InputNum, @MyNumAdd MyNumAdd, @MyNumMinus MyNumMinus,
  FLOOR(@MyNumAdd)FloorNum, CEILING(@MyNumMinus)CeilingNum

SET @MyNum = 265.2
SET @MyNumAdd = @MyNum + .5
SET @MyNumMinus = @MyNum - .4
SELECT @MyNum InputNum, @MyNumAdd MyNumAdd, @MyNumMinus MyNumMinus,
  FLOOR(@MyNumAdd)FloorNum, CEILING(@MyNumMinus)CeilingNum

SET @MyNum = 265.4
SET @MyNumAdd = @MyNum + .5
SET @MyNumMinus = @MyNum - .4
SELECT @MyNum InputNum, @MyNumAdd MyNumAdd, @MyNumMinus MyNumMinus,
  FLOOR(@MyNumAdd)FloorNum, CEILING(@MyNumMinus)CeilingNum

SET @MyNum = 265.5
SET @MyNumAdd = @MyNum + .5
SET @MyNumMinus = @MyNum - .4
SELECT @MyNum InputNum, @MyNumAdd MyNumAdd, @MyNumMinus MyNumMinus,
  FLOOR(@MyNumAdd)FloorNum, CEILING(@MyNumMinus)CeilingNum
SET @MyNum = 265.6
SET @MyNumAdd = @MyNum + .5
SET @MyNumMinus = @MyNum - .4
SELECT @MyNum InputNum, @MyNumAdd MyNumAdd, @MyNumMinus MyNumMinus,
  FLOOR(@MyNumAdd)FloorNum, CEILING(@MyNumMinus)CeilingNum
GO
InputNum     MyNumAdd     MyNumMinus   FloorNum     CeilingNum
------------ ------------ ------------ ------------- ----------
265.1000     265.6000     264.7000        265          265

InputNum     MyNumAdd     MyNumMinus   FloorNum     CeilingNum
------------ ------------ ------------ ------------- ----------
265.2000     265.7000     264.8000        265          265

InputNum     MyNumAdd     MyNumMinus   FloorNum     CeilingNum
------------ ------------ ------------ ------------- ----------
265.4000     265.9000     265.0000        265          265

InputNum     MyNumAdd     MyNumMinus   FloorNum     CeilingNum
------------ ------------ ------------ ------------- ----------
265.5000     266.0000     265.1000        266          266

InputNum     MyNumAdd     MyNumMinus   FloorNum     CeilingNum
------------ ------------ ------------ ------------- ----------
265.6000     266.1000     265.2000        266          266

----------------------------------------