calculation is off by $0.01....any ideas? 2

[nimarii]

hello,

I converted an asp function that generates a mortgage loan schedule to a SQL stored procedure. Everything is working fine, but one of the columns has data that is off by one cent for a few rows.

i cant seem to find out how to correct this, or see where the problem is happening. does anyone have any suggestions on what might be the problem? i feel like i might need to specify rounding, or do i need to convert some fields in the calculations?

many thanks!

 

[vongrunt]

There are probably some calculations with division operator (/) and fixed number of decimals...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[nimarii]

yes, there are quite a few calculations going on, with tons of division operators.

how can i specify to not have a fixed number of decimals?

also, i am using number values such as "100". would it help to use "100.00000000" instead?

 

[SQLSister]

What are your datatypes? If they are real or float, you will never get exact answers after doing math.

Also you may consider if you need to round the calculations as you go to make them match the numbers (and number of decimal places) you would get with a calculator. this is where those errors creep in becasue you think you are dividing 100 into 1000 and the actual numbers in the calculation might be 99.99999999999997/1000.00000000003
This is fine for one interation, but if you do calculations based on it then the differnce gets more and more noticable as the calcluation goes on.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

I'm not familiar with the currency data type because I've never had a need for them, but it may make sense to convert all your money data to the currency data type before doing the calculations.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nimarii]

i'm using float and money data types....

i'm sorry, a bit new at this. but how should i go about rounding in the calculation as i go along? do i need to add

round(@variable, 2)

for every variable in every calculation?

thanks thanks thanks!!!

 

[nimarii]

ok, so adding round(@variable, 2) only increased the error.

 

[SQLDenis]

Take a look at this and you will never use money/floats again

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[nimarii]

wow - thanks Denis.

Hm, so I need to change all money typesto decimal, and all float types to decimal as well?

 

[travisbrown]

i had this problem with a vendor's application, on the calculation of sales tax. The tax on the individual items was calculated on the item price, but the tax subtotal was calculated on the sum of the prices, not as the sum of the tax on each item. In odd cases the tax subtotal would round down on the total, but up on all the individual items. You'd see a discrepancy of $.01.