question

cstephen avatar image
cstephen asked

round off .01 diff

select 1354.44*.528 = 715.14432 Select round(715.14432,2) / .528 = 1354.431818181 We are facing one round of issue error in our production server sql server 2008R2 While round off the first select resulted amount ,It would match the exact amount 1354.44.It will differ 0.01 differences.
sql-server-2008developerdevelopment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
What result are you trying to achieve? If you don't round, you'll get back to your original amount select 1354.44 * .528 -- gives 715.14432 select 715.14432 / .528 -- gives 1354.440000000
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

We are using Two decimal Place, so we used round off
0 Likes 0 ·
If rounding errors are an issue, then there are 2 approaches to use : 1. don't use SQL for presentation layer formatting, let the application do that, so that all values stay as accurate as they can. 2. Don't try and reverse calculations with rounded values, either use the original result (715.14432) or the original value ( 1354.44) For example (and I have no idea what your data represents), but imagine 1354.44 is a cost of some product in it's source currency and 0.528 is the currency conversion rate, to get a local-currency value of 715.44. If you need to know the cost of the product in it's source currency, read it from the database (i.e. 1354.44)
0 Likes 0 ·
Ian Roke avatar image
Ian Roke answered
Can you not just do `select round((1354.44*.528) / 0.528, 2)`? Rounding only needs to be done on the answer. If you round values you do calculations on you loose accuracy. This gives `1354.440000000` which I think is the answer you are looking for?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.