subtraction of different precision values

We have a field which is decimal (9,2) and another which is decimal
(9,3). Is there anyway to subtract the two and get a precision 3
value without changing the first field to 9,3?

For instance, retail value is 9,2, but our costs are at 9,3 due to
being averaged. To calculate margin (retail-cost), we want that also
to be 9,3, but a basic subtraction comes out 9,2. You can see we
don't want to increase retail to be 9,3 (that would look funny), and
it seems wasteful to store retail twice (one 9,2 for users and one 9,3
for margin calc)...is there any other way?

Re: subtraction of different precision values

On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek wrote:

>We have a field which is decimal (9,2) and another which is decimal
>(9,3). Is there anyway to subtract the two and get a precision 3
>value without changing the first field to 9,3?
>
>For instance, retail value is 9,2, but our costs are at 9,3 due to
>being averaged. To calculate margin (retail-cost), we want that also
>to be 9,3, but a basic subtraction comes out 9,2. You can see we
>don't want to increase retail to be 9,3 (that would look funny), and
>it seems wasteful to store retail twice (one 9,2 for users and one 9,3
>for margin calc)...is there any other way?

Hi ibcarolek,

Can you post a repro that demonstrates the issue? If I subtract a
decimal(9,3) from a decimal(9,2), the result has three decimal places,
as demonstrated by the repro below. You are obviously doing something in
a different way than I am - I need to know what before I can help you
solve the issue.

DECLARE @Retail decimal(9,2), @Costs decimal(9,3);
SET @Retail = 12.04;
SET @Costs = 9.833;
SELECT @Retail - @Costs;

Result:

2.207


--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: subtraction of different precision values

On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek
wrote:

>We have a field which is decimal (9,2) and another which is decimal
>(9,3). Is there anyway to subtract the two and get a precision 3
>value without changing the first field to 9,3?
>
>For instance, retail value is 9,2, but our costs are at 9,3 due to
>being averaged. To calculate margin (retail-cost), we want that also
>to be 9,3, but a basic subtraction comes out 9,2. You can see we
>don't want to increase retail to be 9,3 (that would look funny), and
>it seems wasteful to store retail twice (one 9,2 for users and one 9,3
>for margin calc)...is there any other way?

I ran the following test using SQL Server 2000 and 2005 but could not
reproduce the behavior you describe.

DECLARE @field1 decimal(9,2)
SET @field1 = 123.45
DECLARE @field2 decimal(9,3)
SET @field2 = 123.456

SELECT @field1, @field2, @field1 - @field2

----------- ----------- -------------
123.45 123.456 -.006

The result always went to three decimal places.

Having said that, you can explicitly convert before performing the
calculation:

SELECT CONVERT(decimal(9,3),@field1) - @field2
SELECT CAST (@field1 as decimal(9,3)) - @field2

Roy Harvey
Beacon Falls, CT