Hello people, I have an SQL query that performs some profit calculations before the result is displayed.
As I was CFlooping, I noticed that while most results (chargeToCustomer - Cost = Profit) were accurate, some of them were off by 1 cent.
Since I don't know SQL server functions very well, I decided to leave the calculations to Coldfusion.
But I'm getting similar results. Out of 144 records, I have about 10 that are off by 1 cent.
Has anyone experienced something similar?
Is there any decimal function I need to add in order to respect the values? I have the perception that some values are either rounded up or down, which causes the result to be off.
My formula is extremely simple: <cfset myProfit = Charge - Cost>
No magic, not weird formulas and not hidden fees
Thank you in advance!
This can depend on the type of numbers you are talking about. If they are very large numbers then I would suggest looking into PreciseEvaluation - ColdFusion Help | PrecisionEvaluate
If they are not that big then there is something else you are doing but without seeing more code of example numbers its hard to tell.
Thank you for your answer. I think my problem is the cost: it has 4 decimal places. I'll make changes and post results here.
Yup, the issue was related to the Cost. Since the formula included the time spent, it was getting 4 decimal places.
I solved it with some SQL.