Incorrect results when performing arithmetic

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

Transact-SQL (2000)

Incorrect results when performing arithmetic

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2006-03-15 : 08:39:32

Hi,

I've got a stored procedure which needs to return a numeric result.

In the stored procedure, I return a number of values, then do a calculation using the results. The calculation looks like this


(post97.post97val / (pre97.pre97val + post97.post97val)) * pre97.TOAdminCharge

The values in these fields are:
post97.post97val 18930.8900
Pre97.pre97val 1403.9900
pre97.ToAdmincharge 175.0000

Ok, so if I do the calculation on the calculator, I get 162.9173, whereas my sproc gives me 162.9075. These figures represent monetary amounts, so when you round them to 2dp, you get £162.91 instead of the correct value of £162.92.

The thing is, I think I know why it's doing this. All the fields I'm using are held to 4dp, and when I do the division part of the calculation, the result is returned as 4dp (.9309) whereas a calculator gives me a bunch of extra digits which affect the outcome of the calculation (.930956563).

So my question is, how do I make my calculation use 9 decimal places in the calculation?

If this makes little or no sense, I apologise.

Cheers,

Yonabout

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 08:46:13

What are the datatypes of those columns?
Refer this

declare @post97val decimal(12,4)
declare @pre97val decimal(12,4)
declare @ToAdmincharge decimal(12,4)
set @post97val=18930.8900
set @pre97val=1403.9900
set @ToAdmincharge=175.0000

select @post97val/(@pre97val+ @post97val)*@ToAdmincharge as Result,
Round(@post97val/(@pre97val+ @post97val)*@ToAdmincharge,2) as Rounded_Result

Madhivanan

Failing to plan is Planning to fail

yonabout
Posting Yak Master

112 Posts

Posted - 2006-03-15 : 09:23:09

Thanks for that,

I previously had the procedure running as a select referencing aliased tables, so I broke it down to populate the variables individually then ran as you suggested.

Worked a treat.

Thanks again

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources