Substracting not providing accurate results

[Dani Szwarc]

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!

[haxtbh · Accepted solution]

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.

[Dani Szwarc]

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.

[Dani Szwarc]

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.

Thank you!