Hi all
I'm having a strange problem of rounding calculations with decimal variables 
in SQL Server (2008). 
It seems to me that it do not return the correct value when it performs a 
division to 1000000 (one million). 
I have this test script, that anyone can try in Management Studio: 

declare @input table (Consumi decimal(28,10), PCI decimal(28,10))
Insert into @input (Consumi,PCI)
VALUES(3.767, 42621.0000000000)
select * From @input

declare @test table (Consumi decimal(28,10), PCI decimal(28,10), Contenuto 
decimal(38,10))
Insert into @test
select Consumi, PCI, Consumi*PCI/1000000
from @input

Insert into @test
select 3.767,42621, 3.767*42621/1000000
from @input

select * From @test

The results are these: 


Consumi	PCI	Contenuto
3.7670000000	42621.0000000000	0.1605530000
3.7670000000	42621.0000000000	0.1605533070


The values of the last column-in theory-should be equal, but SQL Server me 
returns different (the first stops, I think wrongly at the sixth decimal 
place).

Someone is able to explain this?

Thank you.

Luigi

Hi Luigi

Here is another example of this sort of multiplication which results in a 
"strange" answer.

declare @num1 numeric(38,10)
declare @num2 numeric(38,10)
set @num1 = .0000006
set @num2 = 1.0
select cast( @num1 * @num2 as numeric(38,10))

This article has a very good explanation of why this is designed to happen:

http://blogs.msdn.com/sqlprogrammability/archive/2006/03/29/564110.aspx

By changing the precision in your example, the answers become the same:

declare @input table (Consumi decimal(18,10), PCI decimal(18,10))
Insert into @input (Consumi,PCI)
VALUES(3.767, 42621.0000000000)
select * From @input

declare @test table (Consumi decimal(18,10), PCI decimal(18,10), Contenuto 
decimal(38,10))
Insert into @test
select Consumi, PCI, Consumi*PCI/1000000
from @input

Insert into @test
select 3.767,42621, 3.767*42621/1000000
from @input

select * From @test

Jon

"Luigi" wrote:

> Hi all
> I'm having a strange problem of rounding calculations with decimal variables 
> in SQL Server (2008). 
> It seems to me that it do not return the correct value when it performs a 
> division to 1000000 (one million). 
> I have this test script, that anyone can try in Management Studio: 
> 
> declare @input table (Consumi decimal(28,10), PCI decimal(28,10))
> Insert into @input (Consumi,PCI)
> VALUES(3.767, 42621.0000000000)
> select * From @input
> 
> declare @test table (Consumi decimal(28,10), PCI decimal(28,10), Contenuto 
> decimal(38,10))
> Insert into @test
> select Consumi, PCI, Consumi*PCI/1000000
> from @input
> 
> Insert into @test
> select 3.767,42621, 3.767*42621/1000000
> from @input
> 
> select * From @test
> 
> The results are these: 
> 
> 
> Consumi	PCI	Contenuto
> 3.7670000000	42621.0000000000	0.1605530000
> 3.7670000000	42621.0000000000	0.1605533070
> 
> 
> The values of the last column-in theory-should be equal, but SQL Server me 
> returns different (the first stops, I think wrongly at the sixth decimal 
> place).
> 
> Someone is able to explain this?
> 
> Thank you.
> 
> Luigi
> 

"jgurgul" wrote:

> Hi Luigi
> 
> Here is another example of this sort of multiplication which results in a 
> "strange" answer.
> 
> declare @num1 numeric(38,10)
> declare @num2 numeric(38,10)
> set @num1 = .0000006
> set @num2 = 1.0
> select cast( @num1 * @num2 as numeric(38,10))
> 
> This article has a very good explanation of why this is designed to happen:
> 
> http://blogs.msdn.com/sqlprogrammability/archive/2006/03/29/564110.aspx
> 
> By changing the precision in your example, the answers become the same:
> 
> declare @input table (Consumi decimal(18,10), PCI decimal(18,10))
> Insert into @input (Consumi,PCI)
> VALUES(3.767, 42621.0000000000)
> select * From @input
> 
> declare @test table (Consumi decimal(18,10), PCI decimal(18,10), Contenuto 
> decimal(38,10))
> Insert into @test
> select Consumi, PCI, Consumi*PCI/1000000
> from @input
> 
> Insert into @test
> select 3.767,42621, 3.767*42621/1000000
> from @input
> 
> select * From @test
> 
> Jon

Thank you Jon. So I have to change my precision from 38 to 18, right? 

Luigi

Hi Luigi

You will need to determine what your precision and scale need to be for your 
scenario. 

Although changing it solves this particular problem calculation it may not 
be the best fit for your data.

If you do not need a 10 decimal scale then define a smaller one, and the 
same for precision.

using only the numbers you have shown.

DECLARE @Consumi DECIMAL(4,3),@PCI INT,@Contenuto INT --removed scale 
altogether, they are now int
SET @Consumi = 3.767
SET @PCI = 42621
SET @Contenuto = 1000000
SELECT @Consumi*@PCI/@Contenuto

In the sql above there is less scale but the calculation is accurate.

It is important to define the right types for your data. 

Jon

"Luigi" wrote:

> "jgurgul" wrote:
> 
> > Hi Luigi
> > 
> > Here is another example of this sort of multiplication which results in a 
> > "strange" answer.
> > 
> > declare @num1 numeric(38,10)
> > declare @num2 numeric(38,10)
> > set @num1 = .0000006
> > set @num2 = 1.0
> > select cast( @num1 * @num2 as numeric(38,10))
> > 
> > This article has a very good explanation of why this is designed to happen:
> > 
> > http://blogs.msdn.com/sqlprogrammability/archive/2006/03/29/564110.aspx
> > 
> > By changing the precision in your example, the answers become the same:
> > 
> > declare @input table (Consumi decimal(18,10), PCI decimal(18,10))
> > Insert into @input (Consumi,PCI)
> > VALUES(3.767, 42621.0000000000)
> > select * From @input
> > 
> > declare @test table (Consumi decimal(18,10), PCI decimal(18,10), Contenuto 
> > decimal(38,10))
> > Insert into @test
> > select Consumi, PCI, Consumi*PCI/1000000
> > from @input
> > 
> > Insert into @test
> > select 3.767,42621, 3.767*42621/1000000
> > from @input
> > 
> > select * From @test
> > 
> > Jon
> 
> Thank you Jon. So I have to change my precision from 38 to 18, right? 
> 
> Luigi

Hi Jon,
what do you think is the best solution to have until 10 decimals in my 
calculations?
Indipendently by the precision, I mean.

Luigi

Hi Luigi

if you need to store 10 decimals for your values then use that defined type. 
however if you are only storing 4 decimals, then reduce from 10 to 4.

use as many decimals as you need to store your data.

Jon

"Luigi" wrote:

> Hi Jon,
> what do you think is the best solution to have until 10 decimals in my 
> calculations?
> Indipendently by the precision, I mean.
> 
> Luigi