473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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?

Sep 24 '07 #1
2 7108
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
Sep 24 '07 #2
On Mon, 24 Sep 2007 10:17:15 -0700, ibcarolek <ca*****@ix.netcom.com>
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
Sep 24 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
by: Philipp | last post by:
Hello (not sure this is the right forum for that question so please redirect me if necessary) How can I know how many double values are available between 0 and 1? On my machine (pentium 3) I get...
5
by: DAVID SCHULMAN | last post by:
I've been trying to perform a calculation that has been running into an underflow (insufficient precision) problem in Microsoft Excel, which calculates using at most 15 significant digits. For this...
34
by: Andy | last post by:
Hi, Are 1 through 4 defined behaviors in C? unsigned short i; unsigned long li; /* 32-bit wide */ 1. i = 65535 + 3; 2. i = 1 - 3; 3. li = (unsigned long)0xFFFFFFFF + 3; 4. li = 1...
5
by: bruce.james.lee | last post by:
hi i have a problem with integer subtraction in C. printf("%d", c < (a - b)); a is got from a #define and is 0x80000000 and b is got from input and is also 0x80000000. c is ffffffff (-1)....
15
by: michael.mcgarry | last post by:
Hi, I have a question about floating point precision in C. What is the minimum distinguishable difference between 2 floating point numbers? Does this differ for various computers? Is this...
3
by: muler | last post by:
hi all, After reading this excerpt from "The C# Programming Language", (By Anders) I tried to check it out. Unfortunately, I'm getting compile errors. Can anyone illustrate this with an...
17
by: spooler123 | last post by:
Just a small little program. Can not figure out what am I doing wrong. #include <stdio.h> #include <limits.h> #include <float.h> int main() { double max = FLT_MAX;
5
by: Keflavich | last post by:
Hey, I have a bit of code that died on a domain error when doing an arcsin, and apparently it's because floating point subtraction is having problems. I know about the impossibility of storing...
6
by: Matthew | last post by:
Hi, I want to change the precision level of floating point variables and calculations which is done in php.ini. However the server I rent for my domain does not give me access to php.ini,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.