Jeff Moden (3/4/2013)
Considering that you're only working with 6 digits to begin with, consider transposing your formula for a smidgen more accuracy.
UPDATE dbo.AnalysisSET AccMargin = ((MArgin+Freight)/Gallons)
Of course, since you're doing decimal division, you should as consider expanding the precision and scale of the calculation by doing as Dwain suggested with CAST and then round to the correct number of decimal places.
Not sure such a change in the formula will add any accuracy.
SUM will keep the 6 digits precision, while division will use implicit conversion to FLOAT before and implicit conversion to decimal with higher procision after.
So, there is no need in an explicit expanding, it will be done behind the scene:
SELECT SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'scale'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'scale'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'scale')
FROM #Analysis AS A
Output:
SUMnumeric226
Divisionnumeric3217
Sum of divsnumeric3317
And, of course, adding extra 3 arithmetic operations to the calculation won't increase precision. 🙂
There is need for Dwain's overcomplicated formula.
Simple ROUND will do perfectly good here.
_____________
Code for TallyGenerator