Rounding issues are the bane of every accountants existence. While a few records may only produce a few cents difference a few million records can lead to thousands of dollars or just a penny. Depending on if the rounding occurs at the detail level or the summary level you can get very different results. We coders can also make poor decisions which impact or minimize the error. Assume we have 100 records at 249.754988 after applying a currency conversion. So our true total should be $24975.4988
select convert(numeric(20,2), round(sum(amt),2)) as amt
This bit of code at a Summary level would give us a grand total of $24975.50 (.0012 overstated)
If however we had used the round function at the detail level we would have a mere $24975.00 (.4912 understated)
select convert(numeric(20,2),sum(amt)) as amt
At the summary level we are now losing the decimals beyond the first two through truncation so our grand total is $24975.49
The detailed level would be the same as above.
Imagine if the value was for 1Million records rather than 100 records. The difference would be $4988.00 less than it should be. Still a small percentage, but compounding rounding errors could be even more detrimental. If you must round for formatting, try to do so at a summary level.
