Post reply

Two equivalent math expressions - one with variables, one with constants - give different results

  • August 19, 2011 at 3:51 pm

    #255901

    Can somebody explain why the two math expressions - one with variables, one with constants - give different results?

    declare@amount_remaining decimal(18,2),@gross_fees decimal(18,2),@tier_fees decimal(18,2),@tier_rate decimal(26,10),@tier_units decimal(18,2)

    select@tier_units=5000000000.00,@tier_rate=2.0250000000,@gross_fees=111883775187.72,@amount_remaining=932364.79,@tier_fees=0

    select @tier_fees=round(((@tier_units*@tier_rate)/@gross_fees)*@amount_remaining,2)

    select @tier_fees /* why is this result different from the next result*/

    select round(((5000000000.00*2.025)/111883775187.72)*932364.79,2)

  • August 19, 2011 at 4:29 pm

    #1370993

    An implicit conversion is happening. And when it is converted, a truncation is occurring.

    Try this.

    select round(((CAST(5000000000.00 AS DECIMAL(18,2))*CAST(2.0250000000 AS DECIMAL(26,10)))/111883775187.72)*932364.79,2)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 20, 2011 at 5:48 am

    #1371049

    I am trying to figure out how to get the right result using the variables. The expression with the numbers as constants produces the correct result.

  • August 20, 2011 at 6:24 am

    #1371051

    I changed all of my variable data tyes to float and the variable-based expression produced the desired result.

    I also found that decimal (38,10) also works, so I might do that as instead because I always understood float to be not recommended for calculations where accuracy to the penny is needed.

    from:

    http://msdn.microsoft.com/en-us/library/ms187912.aspx

    "

    Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types."

  • August 20, 2011 at 3:51 pm

    #1371095

    Make this change and it will work in that case

    ,@tier_rate decimal(18,10)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 24, 2011 at 8:00 am

    #1372587

    This should help explain the issue you are seeing.

    http://bit.ly/nL7s1m

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 6 posts - 1 through 5 (of 5 total)

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