sp to reconcile rounding discrepancy

[smhaig]

I have a table as such:
ID cost
1 654.77
2 953.54
3 345.98
...

for each of these ID's I have in a child table a list of percentages that equal 100 for each ID: (the percent has 4 dec places but here putting 2 for symplicity.

ID percent
1 .50
1 .50
2 .33
2 .33
2 .34
3 .24
3 .76
etc.

I have a sp that calculates an amount for each id for each percent. Here is a part of it. This caluclates this for each id.

(@jetempalloc has a record for each ID with fields:ID, cost, percent, damt
)
update @jetempalloc
set dacct=rtrim(fund)+ ' ' +drcode,
damt = (apercent * cost)

The problem is that the results in damt do not add up to the cost. There is often a discrepancy of a few cents, due to rounding. I have to go back and create a query to add a few cents to one of the damt's for each ID so that the sum for each ID will add up to the cost.

Any ideas for this query. Is there a better way to do this in the initial sp that I show above?

Thanks
smhaig (sql server 2000)

 

[RAS56]

I don't see that anyone else has jumped in here so I'll give my 2 cents worth.

This problem has cropped up several times over the years in stuff I've written. The way I tend to handle it is to define all the percentages except one and then back into that percentage based on the others.

For example, in your scenario, I would have the following in my percentage table:

ID percent
1 .50
2 .33
2 .33
3 .24

Then my caluculations would be something like cost * percent for each matching record in the percent table and cost - (cost * sum(percent) for id=id) for the percentage not covered by the percent table.

Hopefully, this makes sense. If not, I can dummy up some data and show it a little better.

 

[smhaig]

Yes, I see what you are doing --(in the final cost-(cost * sum(percent) -- that percent would be the sum of all the percents that have been processed, I am presumming. So if you have done .33 and .33, then that calc is cost - 66 = 34 which would be the amount for the last percentage that was not calculated (.34))

However, How can I do this if the percent table has all the percents in it already. I would need the sp to not calc the last percentage of each particular ID.

Is there a way to do that?

 

[RAS56]

I'm not sure I see a way to do this with those data structures without adding an identity column to the percentages table. If you have the option of adding that column, you may then also have the option of changing the data which imo would be better and simpler.

Anyhow, I am not very good at these kind of queries but here is an example using an identity column on the percentages table to get what you want. Probably some of the folks that frequent this forum a lot can give you something a lot more elegant and understandable:

create table costs (costid int identity, amount money)
insert into costs (amount) values (654.77)
insert into costs (amount) values (953.54)
insert into costs (amount) values (345.98)

create table percentages (percentageid int identity, costid int, percentage float)
insert into percentages (costid,percentage) values (1,.50)
insert into percentages (costid,percentage) values (1,.50)
insert into percentages (costid,percentage) values (2,.33)
insert into percentages (costid,percentage) values (2,.33)
insert into percentages (costid,percentage) values (2,.34)
insert into percentages (costid,percentage) values (3,.24)
insert into percentages (costid,percentage) values (3,.76)

select x.costid, y.percentage, x.amount*y.percentage from costs x, percentages y 
 where y.costid=x.costid
   and y.percentageid not in
(
select max(percentageid) from percentages group by costid
)

union all

select x.costid, 1-sum(y.percentage), x.amount-(x.amount*sum(y.percentage)) from costs x, percentages y 
 where y.costid=x.costid
   and y.percentageid not in
(
select max(percentageid) from percentages group by costid
)
group by x.costid, x.amount
order by x.costid

drop table costs
drop table percentages