josereyes
asked on
MS-SQL calculation acccuracy
Analyse this....
Have a table with two money columns.
One column filled with individual totals.
Sum of these totals is 270.36.
Other column needs to get filled with %
of that total.
OK , say one entry is 56.4500. Percentage calulation in SQL is giving 20.8700, which is wrong. Why are last two decimal places zero in SQL but any
calculator gives 20.8795 to four decimal places ? Thought money had a scale of 4 ?
Also tried the simple calculation of 56.45
divided by 270.36 and result into a variable.
Money variable gives 0.21, a decimal(10,4) variable gives 0.2088.
We have declared all our money value fields as money type in the table design.
We are worried about acccuracy now !!
Can someone advise please ?
Have a table with two money columns.
One column filled with individual totals.
Sum of these totals is 270.36.
Other column needs to get filled with %
of that total.
OK , say one entry is 56.4500. Percentage calulation in SQL is giving 20.8700, which is wrong. Why are last two decimal places zero in SQL but any
calculator gives 20.8795 to four decimal places ? Thought money had a scale of 4 ?
Also tried the simple calculation of 56.45
divided by 270.36 and result into a variable.
Money variable gives 0.21, a decimal(10,4) variable gives 0.2088.
We have declared all our money value fields as money type in the table design.
We are worried about acccuracy now !!
Can someone advise please ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you store money data without specifying a monetary symbol is is stored as an approximate (floating point number), like the float data type. When you do specify the monetary symbol, it is stored as an exact value, like decimal(X,4).
Hope this helps...
Hope this helps...
It's time to clean up this topic area and that means taking care of this question. Your options at this point are:
1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.
2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
3. Ask Community Support to help split points between participating experts. Just comment here with details.
4. Delete the question because it is of no value to you or to anyone else. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there. We also request that you review any other open questions you might have and update/close them. Display all your question history from your Member Profile to view details.
PLEASE DO NOT AWARD THE POINTS TO ME.
__________________________
Hi Experts:
In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question. Likewise, you can also suggest that I PAQ or delete the question.
Experts, please do not add further "answer" information to this question. I will be back in about one week to finalize this question.
Thank you everyone.
Moondancer :)
Community Support Moderator @ Experts Exchange
P.S. Engineering has been advised about the error in the comment date/time sort order.
1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.
2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
3. Ask Community Support to help split points between participating experts. Just comment here with details.
4. Delete the question because it is of no value to you or to anyone else. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there. We also request that you review any other open questions you might have and update/close them. Display all your question history from your Member Profile to view details.
PLEASE DO NOT AWARD THE POINTS TO ME.
__________________________
Hi Experts:
In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question. Likewise, you can also suggest that I PAQ or delete the question.
Experts, please do not add further "answer" information to this question. I will be back in about one week to finalize this question.
Thank you everyone.
Moondancer :)
Community Support Moderator @ Experts Exchange
P.S. Engineering has been advised about the error in the comment date/time sort order.
josereyes,
These questions are still open and our records show you logged in recently. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11324697
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11723798
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20164448
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20165555
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20173349
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20187667
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20193216
EXPERTS: Please leave your thoughts on this question here.
Thanks,
Netminder
Community Support Moderator
Experts Exchange
These questions are still open and our records show you logged in recently. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11324697
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11723798
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20164448
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20165555
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20173349
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20187667
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20193216
EXPERTS: Please leave your thoughts on this question here.
Thanks,
Netminder
Community Support Moderator
Experts Exchange
No response to my July 2001 request, I'd say split to close, no waiting period. Moderator requests ignored by Asker.
EE Moderator
EE Moderator
Admin notified of user neglect. Force/accepted by
Netminder
Community Support Moderator
Experts Exchange
jboyd: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20272272
Netminder
Community Support Moderator
Experts Exchange
jboyd: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20272272
select convert(money,convert(mone
gives .2088.
In 6.5 money is held and calculated to 4 decs but displayed by default truncated to 2.
Try converting the result to dec to see the full precision.