Saturday 19 January 2013

All about ROUNDING

Introduction

ROUNDING is a very important factor in Mathematical calculations. In MS SQL Server we offend use different function to round the mathematical data. I saw that developer face a common problem when rounding data. It may be mathematical rounding or financial rounding. The main cause of facing problem, it is not to shame to say a lot of developer doesn't have any idea about rounding and how it works. In this article I am trying to clear all the problem related to rounding. Hope you like it and find it useful.

Tropics to discuss

1.    What the Logic Behind Rounding
2.    MS SQL Server and Rounding
3.    Financial Rounding

What the Logic Behind Rounding

As I am not a mathematician, so I follow some simple rules for mathematical rounding.

1.    If the last digit is less than 5 – you must drop it.
2.    If the last digit is greeter then 5 – you must drop it and increase the second to last digit by 1.
3.    If the last digit is equal to 5 -- drop the five and increase the second to last digit by one if it is odd, and do not increase the second to the last digit if it is even.

If we take an example

1.    Rounding 12.71   =   12.7
2.    Rounding 12.79   =   12.8
3.    Rounding 12.75   =   12.8   and Rounding 12.85  =  12.8  


MS SQL Server and Rounding

In MS SQL Server the rounding function depends on the data type.
Depends upon the data type (Int, float, decimal etc) the rounding value may be different. The MS SQL Server provide 3 types of rounding functions ROUND(), CEILING() and FLOOR() and depends on the function the value is different.

ROUND()

It returns a numeric value and round up to specified length or precisions.
The syntax is

ROUND ( numeric_expression , length [ ,function ] )

Numaric_Expressions

This can be positive or negative numbers and data type can be Int, decimal, numeric, money or small money.
If the length is an negative number in this case

Example
Result
ROUND(748.58, -1)
750
ROUND(748.58, -2)
700
ROUND(748.58, -3)
Numeric Over flow

In this Example of ROUND() the Last digit is always estimated

Example
Result
ROUND(123.9994, 3)
123.9990
ROUND(123.9995, 3)
123.0000

In this Example of ROUND() Showing approximations

Example
Result
ROUND(123.4545, 2)
123.4500
ROUND(123.45, -2)
100.00

In this Example of ROUND() Showing round and truncation

Example
Result
ROUND(150.75, 0)
151.00
ROUND(150.75, 0, 1)
150.00


CEILING()

Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression. It accepts one value (Value to round).

Example:

SELECT CEILING(123.45) AS 'CEILING(123.45)',
       CEILING(-123.45)AS 'CEILING(-123.45)',
       CEILING(0.0)    AS 'CEILING(0.0)'

CEILING(123.45)       CEILING(-123.45)      CEILING(0.0)
124                              -123                             0

FLOOR()

Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression and accepts one value (Value to round).

SELECT FLOOR(123.45) AS 'FLOOR(123.45)',
       FLOOR(-123.45)AS 'FLOOR(-123.45)',
       FLOOR(0.0)    AS 'FLOOR(0.0)'


FLOOR(123.45)         FLOOR(-123.45)        FLOOR(0.0)
123                              -124                             0


Financial Rounding

If I have a fanatical report showing

Srl. No
Description
Amount in Rs
1
Item-1
1000.15
2
Item-2
1000.25
3
Item-3
1000.35
4
Item-4
1000.45
Total
4001.20

If you say a customer to pay Rs. 4001.20 you definitely get some …..
So it should be rounded up to Rs. 4001.00. In another example if the grand total is Rs. 4001.71, it should be rounded up to Rs. 4002.00.

To get this type of rounding we can make an UDF of MS SQL Server. Here we are taking a concept of Round off (+) or Round off (-).


IF OBJECT_ID('dbo.fn_finantialRound') IS NOT NULL
   BEGIN
     DROP FUNCTION dbo.fn_finantialRound;
   END
GO
CREATE FUNCTION fn_finantialRound
(
      @p_RoundNumber DECIMAL(20,2)
)
RETURNS DECIMAL
AS
BEGIN
      DECLARE @v_RerVal   DECIMAL(20,2),
              @v_RndZero  DECIMAL(20,2),
              @v_OrgNo    DECIMAL(20,2),
              @v_StoreVal DECIMAL(20,2);
     

      SET @v_OrgNo    = @p_RoundNumber;
      SET @v_RndZero  = ROUND(@p_RoundNumber, 0);
      SET @v_StoreVal = @v_RndZero - @v_OrgNo;
     
      IF @v_StoreVal > 0
         BEGIN
            SET @v_RerVal = @v_OrgNo + @v_StoreVal;
         END
      ELSE IF @v_StoreVal < 0    
              BEGIN
                 SET @v_RerVal = @v_OrgNo - ABS(@v_StoreVal);
              END
           ELSE
              BEGIN
                 SET @v_RerVal = @p_RoundNumber;
              END  

     
      RETURN @v_RerVal;

END
GO

-- Testing
SELECT dbo.fn_finantialRound(12.27) -- 12.00
SELECT dbo.fn_finantialRound(12.77) -- 13.00
  

Hope you like it.


Posted by: MR. JOYDEEP DAS

6 comments:

  1. Thank you Joydeep! Its realy helping me to revise "small but too much important points".

    ReplyDelete
  2. I think we are missing small thing here!

    In this Example of ROUND() the Last digit is always estimated
    Example Result
    ROUND(123.9995, 3) here result should be 124.0000

    ReplyDelete
  3. I would like to add one interesting thing related to Round function :
    1.Select Round (784.57,-4) ---will return zero.

    Where as
    2. Select Round(784.57,-3) will return an arithmetic overflow
    Because 784.57,-4 default is decimal (5, 2), which cannot return 1000.00.

    ReplyDelete
    Replies
    1. Thanks “Vikash”
      Thanks to sharing tour view. I really like this if any one share his/her view on my article. It is not only help me to better understand the technology but also useful for my readers and beginners.

      Delete
  4. Hello Joydeep,

    I have got another version -

    CREATE FUNCTION [dbo].[udf_RoundInMultipleOf]
    (
    @Value AS DECIMAL(12,3),
    @MultipleOf AS DECIMAL(12,3),
    @ForceCeiling AS BIT
    )
    RETURNS DECIMAL(12,3)
    AS
    BEGIN

    DECLARE @RoundedValue AS DECIMAL(12,3)

    -- Calculate Rounded value.
    SET @RoundedValue =
    CASE
    WHEN ISNULL(@Value,0) = 0 THEN 0
    WHEN ISNULL(@MultipleOf,0) = 0 THEN 0
    WHEN ISNULL(@ForceCeiling,0) = 0 AND (@Value % @MultipleOf) < (@MultipleOf / 2.0) THEN FLOOR(@Value / @MultipleOf) * @MultipleOf
    ELSE CEILING(@Value / @MultipleOf) * @MultipleOf
    END

    RETURN ISNULL(@RoundedValue,0)
    END


    Examples :
    SELECT [dbo].[udf_RoundInMultipleOf](12.27,1,0) = 12.000
    SELECT [dbo].[udf_RoundInMultipleOf](12.77,1,0) = 13.000

    Hope, you & the readers will find this useful as well.

    --Vinay
    http://vinay.inkeysolutions.com
    http://inkeysolutions.com

    ReplyDelete