Posted by Hugo Kornelis on 09/24/07 17:48

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

[Back to original message]