loss of scale when performing calculation

I'm performing a calculation and I don't get the answer I expect. I lose some scale doing the calculation.

Calc is: 651/1000 * -413.72063274 = -269.33213191 (to 8 d.p)

In SQL Server I do this:

declare @var numeric(28,8)
declare @a numeric(28,8)
declare @b numeric(28,8)

set @var = -413.72063274
set @a   = 651.00000000
set @b   = 1000.00000000

select CAST((@a/@b) * @var as numeric(28,8)) as result_1
     , CAST(CAST(@a as numeric(28,8)) 
      /CAST(@b as numeric(28,8)) as numeric(28,8)) 
      *CAST(@var as numeric (28,8))   as result_2

The results is 开发者_Go百科

result_1: -269.33213200 (correct to 6dp)

result_2 : -269.332132 (correct to 6dp)

How do I get the query to return: -269.33213191 (correct to 8dp)?

The rules for decimal to decimal conversion for multiplication and division are described in BOL.

*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

but leaves it unspecified exactly how such truncation is performed. This is documented here. However sometimes it is easier just to use trial and error!

The following intermediate casts give you the desired result. Can you live with those?

DECLARE @var NUMERIC(19,8)
DECLARE @a NUMERIC(19,8)
DECLARE @b NUMERIC(19,8)

SET @var = -413.72063274
SET @a   = 651.00000000
SET @b   = 1000.00000000

DECLARE @v SQL_VARIANT 
SET @v =  CAST(@a/@b AS NUMERIC(24,10))* CAST(@var AS NUMERIC(23,8))

SELECT    CAST(SQL_VARIANT_PROPERTY(@v, 'BaseType') AS VARCHAR(30)) AS BaseType,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Precision') AS INT) AS PRECISION,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Scale') AS INT) AS Scale

For this specific case, you could cheat by first multiplying all values and dividing the final result back with this multiplier.

declare @var numeric(28,8)
declare @a numeric(28,8)
declare @b numeric(28,8)
declare @m numeric(28,8)

set @var = -413.72063274
set @a   = 651.00000000
set @b   = 1000.00000000
set @m   = 10000000


select CAST(((@a*@m) * (@var*@m) / (@b*@m)) AS NUMERIC(28, 8)) / @m 
-- Result: -269.3321319137

Edit

on the other hand, following retains its precision without using a multiplier

select CAST(@a * @var AS NUMERIC(28, 8)) / @b
-- Result: -269.3321319140

You start with division and after that multiply. The loss of precision is in the division. This is rounded to 8 dp and the result is multiplied by a 3 digit (413) value (hence the missing last two digits)

A solution would be to use larger precision for intermediate results, or do your multiplication first.

(@a * @var) / @b is mathematically equal to your calculation, but might give more accurate results.