Rounding inconsistency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rounding inconsistency

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-23 : 22:52:20

I searched the forum but couldn't find anything .. I'm sure it's somewhere out there. Anyway, I have a procedure the performs the following calculation:

	QTotalCost = (Quantity * UnitRate * (1 + PSTRate) * USRate), 
	QTotalCostRnd = ROUND((Quantity * UnitRate * (1 + PSTRate) * USRate), 2)

Here are some of the results:

20003.025 20003.02 ... in this case it does not round up
474.525 474.53 ... in this case it rounded up

What method of madness does the ROUND use to come up with these results?
Do I need to add .00000001 or whatever to get it to work properly?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 22:55:25

can you post some sample data for Quantity, UnitRate, etc.. ?
Pleas also specify the data type for those columns
----------------------------------
'KH'
It is inevitable

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-24 : 00:01:17

Quantity = float
UnitRate = float
PSTRate = float
USRate = float

Quantity = 81.645
UnitRate = 245
PSTRate = .07
USRate = 1
QTotalCost = 20003.025
QTotalCostRnd = 20003.02 ... rounds down

Quantity = 81.641
UnitRate = 245
PSTRate = .07
USRate = 1
QTotalCost = 20002.045
QTotalCostRnd = 20002.05 ... rounds up

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-24 : 00:05:31

Oops .. PSTRate in both cases = 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 00:43:49

suspect this is due to float. float data type is an approximate. Try using numeric / decimal.

----------------------------------
'KH'
It is inevitable

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-24 : 01:27:15

I'm using float because the user can enter as many decimal places as they want. Not sure where to go from here ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 02:09:38

"the user can enter as many decimal places"
There must be a limit right on how many decimal places they can enter ?

"Not sure where to go from here ..."
Convert the decimal(18, 8) before rounding ? 8 decimal places should be sufficient ?

----------------------------------
'KH'
It is inevitable

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-24 : 11:40:38

Is it just the rounding that causes problems with FLOAT or are there other considerations as well?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-02-24 : 22:59:24

Acording to Books Online float is an Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented. The decimal and numeric datatypes (they are the same) have a precision of 38 by default which means that you can have 38 digits total, both in front of and after the decimal point. Example: DECIMAL(18, 8) will give you 10 decimal places in front of the decimal point and 8 after. DECIMAL(38, 30) will give you 8 decimal places in front of the decimal point and 30 after. Should be sufficient for most people I belive...?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-26 : 15:05:18

I suppose then I should stay away from using float and just use Decimal. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources