Problem with GST rounding and totalling
-
I have the following problem
I have a view which has a NetCost figure calculated as Rate Per Item * No of Items
Then there is a GST column which is 10% of this for each item. The problem is if I sum the GST amounts at the end is not exactly 10% of the NetCost toal because the GST gets rounded for each item, resulting in a total GST figure that is less than 10% of the summed NetCost total.
I tried rounding GST to three decimal places, then casting as two decimal places, but casting to two decimal places and summing results in the same problem. Can I round to three decimals, which gives me the figure I need, but then cut off or suppress the most right hand decimal place so I am still summing correctly but just hiding the third decimal place from the value returned
OR
Am I approaching this the wrong way completely?
-
You mentioned that the GST component is rounded per item/record. In this instance, depending on the rounding that is used, you may not be able to reconcile the summary data.
The more decimal places (ie. accuracy) that is used the better. Otherwise, you will have to perform a recalculation and round the figures to an appropriate number for accuracy. On the other hand, maybe someone with an accounting background might be able to help you better.
-
Actually, they pay me good salary for solving problems like this.
Including rounding issue in reports.
Will you?
_____________
Code for TallyGenerator -
Sorry Sergiy, what is it you wanted to know?
-
I think SQL Server always rounds the 5s up, and that gives you a biased estimator (considered with a uniform distribution).
-
Me?
Nothing.
Your question is not about T-SQL.
Supply your algorithm and we will help you to implement it in SQL.
But seems solution is what you need.
_____________
Code for TallyGenerator -
Troy,
I agree with Sergiy. You need to have precise description of the process first, only then you can try to implement it in SQL.
Somebody has to tell you what they want to get and WHY. If they want to have precise sum, then you maybe have to calculate the GST-sum as a 10% from the sum, not as a sum of partial (item) GST's.
Of course it is possible to display only part of the number (less decimal places than it has stored), but is that really what you need? There is no way how we could decide this, that is up to you - and to those colleagues who have asked you to code this report or whatever it is.
-
Troy,
This is a common problem in Canada, which has a blanket 7% GST rate in every province for most products. Most of the implementations I've seen do not track a GST value per item. Rather, they track whether an item is GST-applicable or not. To calculate the total GST on, say, an invoice, calculate the total of all items subject to GST, apply your GST rate, and this becomes the total GST for the invoice.
You will never get matching sums by comparing the total GST on the invoice to the sums of the individual GST amounts on each line item like you are describing.
Hope this helps.
Vik
-
Thank you, that is helpful
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply