• CELKO (12/15/2010)


    =================

    DECLARE

    @mon1 MONEY,

    @mon2 MONEY,

    @mon3 MONEY,

    @mon4 MONEY,

    @num1 DECIMAL(19,4),

    @num2 DECIMAL(19,4),

    @num3 DECIMAL(19,4),

    @num4 DECIMAL(19,4);

    SELECT

    @mon1 = 100, @mon2 = 339, @mon3 = 10000,

    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3

    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,

    @num4 AS numericresult

    Output: 2949.0000 2949.8525

    The problem here isn't necessarily in the money type, but it's in the override that occurs.

    In particular, @Num1/@Num2 turns into either a float, or a decimal (20, 20)... leading me to assume the float.

    Expanding on your code above:

    DECLARE

    @mon1 MONEY,

    @mon2 MONEY,

    @mon3 MONEY,

    @mon4 MONEY,

    @num1 DECIMAL(19,4),

    @num2 DECIMAL(19,4),

    @num3 DECIMAL(19,4),

    @num4 DECIMAL(19,4);

    SELECT

    @mon1 = 100, @mon2 = 339, @mon3 = 10000,

    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = @mon1/@mon2*@mon3

    SET @num4 = @num1/@num2*@num3

    SELECT @mon4 AS moneyresult,

    @num4 AS numericresult

    select @mon1, @num1

    select @mon2, @num2

    select @mon3, @num3

    SELECT @mon1/@mon2, @Num1/@Num2

    SELECT CONVERT( DECIMAL( 19, 4), @Num1) / CONVERT( DECIMAL( 19, 4), @Num2)

    SELECT CONVERT( DECIMAL( 19, 4), @Num1 / @Num2)

    SELECT @Mon2*@Mon3, @Num2*@Mon3

    SELECT 0.2949*10000, 0.2949852507374631268 * 10000

    In particular, the results from SELECT @mon1/@mon2, @Num1/@Num2 are incredibly disparate, which is causing the difference.

    The reconversion from the float into the correct # of significant digits also changes the resultant rounding, a known issue with float. It depends on what level of accuracy to what significance you want.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA