Help With Decimal Places (please!)
-
In a stored procedure, I am trying to get the sp to divide one money figure in a column by another money figure in a column to arrive at a decimal result of the amount paid. This will need to calculate all 40,000 odd rows. Perhaps an example might suffice:
On a calculator, if I divide the amount paid (457.00) by the original amount of the fee (460), i get an answer of 0.99347826086956521739130434782609 (being the percentage of the fee that was paid). In SQL, I get 0.99350000! This is not sufficient as I need at least eight decimal places for accuracy. Does any kind soul know how to force SQL to divide correctly and give me at least eight decimal places?
All help appreciated!
Diarmuid
-
Wow! How accurate do you need to be?
The Money datatype in SQL Server helpfully rounds to 4dp, so that's possibly where things are going wrong for you. If you try the following, it may lead you in the right direction:
declare @money1 as money, @money2 as money, @result as decimal(19,8)
set @money1 = 457
set @money2 = 460
set @result = cast(@money1 as float) / cast(@money2 as float)
select @result
Regards
Phil
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi,
See if this helps, basically the same as above, just lets SQL do the cast.
Create Table Test(Fee money, Paid money)
insert into Test Values(460.00, 457.00)
select (Paid) / (Fee * 1.0) from Test
Cheers!
Hanno
-
Thank you both,
Each solution cures my problem (accracy required as tax is being calculated on some large figures and liability arises if a error arises). I've used Hanno's solution as it does not involve altering my already complicated SP.
Once again, a very big thank you.
Diarmuid
-
This is more of a workaround than an true solution. SQL Server evaluates 1.0 as a non money datatype (FLOAT or DECIMAL).
Here's a script by SQL Server MVP Steve Kass from the MS newsgroups:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/@m2
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0028 .0029
(1 row(s) affected)
Both calculations are correct according to the specifications of each datatype. However, MONEY is more than 3% off the exact result.
Now we change it a little bit to
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/(@m2*@m1)
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0028 .0029
(1 row(s) affected)
Obviously no change in the results. Now again a little change:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @d1 = 1.00
set @m3 = @m1/(@m2*@d1)
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0029 .0029
(1 row(s) affected)
See what happens? Same results you get when using a FLOAT as *1.0 in the multiplication.
By any chance avoid money when you need to perform division and multiplication and expect a high degree of accuracy. Always use DECIMAL instead.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply