SQL Server casting result of arithmetic

Question

I'm just looking at some very minor discrepancies in a report I'm putting together and I'm confused as to what is the best practice in SQL server for casting and if somebody can me point in the right direction to explain what I'm seeing.

A very small error of 1p creeps in in a few cases using the current calculation on data similar to that shown in the example table @data.

Could somebody point me in the right direction or at least tell me what it is I'm looking at so I can research further?

The code below produces example output:

    declare @data table (qty decimal(24,12), price decimal(24,12), multiplier decimal(24,12), rate1 decimal(24,12), rate2 decimal(24,12))

    insert into @data
        select 21505.000000000000, 30.475000000000, 1.000000000000,1.166500000000, 1.166500000000


    select round(data.current_method, 2) as current_method_round, round(data.expected_result_method, 2) as expected_result_round, * from 
        (select 
        ((qty * price * multiplier) /rate1) * rate2 as current_method,
         cast(cast(cast(qty * price * multiplier as decimal(24,12)) / rate1 as decimal(24,12)) * rate2 as decimal(24,12)) as expected_result_method,
        ((21505.000000000000 * 30.475000000000 * 1.000000000000) / 1.166500000000) * 1.166500000000 as checkvalue, 
        *
        from @data ) data

Answer 1

Ed... don't do it in SQL! This kind of stuff looks horrible, and should be in middleware where the implications of casting results can be better understood.

[EDIT] I suspect something is being converted into floating point somewhere, probably relating to the division operator. 1p can't be proprerly represented as a floating point number.

Answer 2

You should probably have a look at this article to understand the precision and scale effects on expressions, since multiplying decimals requires precision to increase.

The fact that you get two different results from this:

       ,((qty * price * multiplier) / rate1) * rate2 as current_method
       ,((qty * price * multiplier) * rate2) / rate1 as current_method2

Implies that the intermediate expression in your current_method has a precision problem.