Post reply

SQL 2005 rounding

  • July 11, 2007 at 3:59 am

    #173445

    Hi,

    We are running a few queries (with the same calculations) where the rounding results are diiffering - and we can't see why.

    The calculation that is resulting in differening results is:

    ROUND

    (policytables.[Monthly life Amount], 2) - ROUND(policytables.[Monthly life Amount] * PolicyType.[Gross Business Commission], 2) AS [Net to Insurer from life mta]

    [Monthly life amount]=2.35 (exactly, i.e. no rounding applied), datatype=money

    [Gross Business Commission]=0.3(exactly, i.e. no rounding applied), datatype=real

    Now this is the weird bit as it is giving 1.64 in one query and 1.65 in another.

    The joins are identicle, and there is no grouping.  The above sum is also identical.

    The queries are quite large, but we can post here if required.

    Any help much appreciated

    Dan

  • July 11, 2007 at 5:20 am

    #718564

    Hi Dan,

    I've found some interesting results with your code.

    What have found is that the REAL datatype is loosing one decimal space and in turn is change the result when you do a rounding.

    If you change both types to a Money datatpye you will find that the results are what you will find on a calculator.

    From what I have read, it is fairly common to find problems with calculations that involve the Datatypes Real and or Float.

    Here is what I ran to come to this conculsion

    DECLARE @VrlReal Real

    DECLARE @VmoMoney Money

    SELECT @VrlReal = 0.3

    ,@VmoMoney = 2.35

    SELECT ROUND(@VmoMoney, 2) [Money Rounded]

    SELECT @VmoMoney * @VrlReal [Multiply without Rounding]

    SELECT ROUND(0.705,2) [Figures with Rounding]

    SELECT ROUND(@VmoMoney * @VrlReal, 2) [Variables with Rounding]

    SELECT ROUND(@VmoMoney, 2) - ROUND(@VmoMoney * @VrlReal, 2) AS [Result]

    If you change both data types to Money you will see the round will be "correct" depending on what you actually want your result to be.

    I hope this helps

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 11, 2007 at 5:31 am

    #718566

    Thanks mate.... you really put effort onto that.  Wish i could mark the post as useful for others

    Odd how that works really.

    Thanks for all the effort on this one.

    D

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply