SQL Server Arredondamento

ProblemI saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application.  My users and me have a difference of opinion on some of the calculations in our reporting applications.  All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules.  Do you have any insight into these issues?  I would like to see some examples with a variety of coding options.
SolutionRounding can become misunderstood if the underlying data types and rounding functions are not understood.  Depending on the data type (integer, float, decimal, etc.) the rounded value can be different.  In addition, depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation the values can differ as well.  As such, it is important to find out the user rounding requirements then translate those requirements into the appropriate T-SQL command. 
From a definition perspective, let's start here:

• ROUND() - Rounds a positive or negative value to a specific length.
• CEILING() - Returns the smallest integer greater than, or equal to, the specified numeric expression.
• FLOOR() - Returns the largest integer less than or equal to the specified numeric expression.

Let's walk through each function with a few different data types to see the results.

ROUND(), CEILING() and FLOOR() Examples
Example	Value
In this example you can see with a positive integer all three rounding functions return the same value.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1)SELECT CEILING(@value)SELECT FLOOR(@value)	6 6 6
In the second example even with a negative integer all three rounding functions return the same value.
DECLARE @value int SET @value = -11 SELECT ROUND(@value, 1) SELECT CEILING(@value) SELECT FLOOR(@value)	-11 -11 -11
To prove the point with an integer, rounding is not possible.  Let's check out some other data types.
DECLARE @value int SET @value = -11.5 SELECT ROUND(@value, 2) SELECT CEILING(@value) SELECT FLOOR(@value)	-11 -11 -11
With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example.  The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value.  In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.
DECLARE @value decimal(10,2)SET @value = 11.05 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2)SELECT ROUND(@value, 3)SELECT CEILING(@value)SELECT FLOOR(@value)	11.10 11.05 11.05 12 11
As is the case with the example above, the 6 in the second digit to the right of the decimal place is significant based on the various length parameters.
DECLARE @value decimal(10,2)SET @value = -14.46 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2)SELECT ROUND(@value, 3)SELECT CEILING(@value)SELECT FLOOR(@value)	-14.50 -14.46 -14.46 -14 -15
This example helps to illustrate the break down of rounded values across.  This example also proves the point that the CEILING and FLOOR functions round to the nearest integer.
DECLARE @value decimal(10,10) SET @value = .5432167890 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2) SELECT ROUND(@value, 3) SELECT ROUND(@value, 4) SELECT ROUND(@value, 5) SELECT ROUND(@value, 6) SELECT ROUND(@value, 7) SELECT ROUND(@value, 8) SELECT ROUND(@value, 9) SELECT ROUND(@value, 10) SELECT CEILING(@value) SELECT FLOOR(@value)	0.5000000000 0.5400000000 0.5430000000 0.5432000000 0.5432200000 0.5432170000 0.5432168000 0.5432167900 0.5432167890 0.5432167890 1 0
In the final example, with a float data type you can see the same type of behavior as was the case with the decimal example above.  In addition, the CEILING and FLOOR functions round to the nearest integer.
DECLARE @value float(10) SET @value = .1234567890 SELECT ROUND(@value, 1) SELECT ROUND(@value, 2) SELECT ROUND(@value, 3) SELECT ROUND(@value, 4) SELECT ROUND(@value, 5) SELECT ROUND(@value, 6) SELECT ROUND(@value, 7) SELECT ROUND(@value, 8) SELECT ROUND(@value, 9) SELECT ROUND(@value, 10) SELECT CEILING(@value) SELECT FLOOR(@value)	0.1 0.12 0.123 0.1235 0.12346 0.123457 0.1234568 0.12345679 0.123456791 0.123456791 1 0

Fonte: http://www.mssqltips.com/sqlservertip/1589/rounding-functions-in-sql-server/