Solved

Adjust individual amounts in table so sum of lines = total in another table

Posted on 2013-09-04
Medium Priority
425 Views
Hi Experts,

I have the following issue: I'm splitting costs among different line items and with the rounding, the sum of the split costs is not always adding to the total. How can I update my split data to add or subtract the 1 or 2 cents difference?

Table 1 looks like this:
Cost ID, Amount
1               250
2               500
3               400

Table 2 looks like this:
Cost ID, LineID, Amount
1               1               200
1               2               50
2               2               400.00
2               3               99.99
3               1               400

In this example, the sum of cost IDs 1 & 3 in Table 2 are equal to Table 1 and all is well. However, for cost ID 2, the total is 499.99 rather than 500.
I need to update one of the lines so the total matches.
The line IDs are not necessarily sequential, but they are unique within each cost ID, i.e. you won't have two duplicate line IDs for a single cost ID.

Thomas
0
Question by:nutsch
• 4
• 3
• 2

LVL 35

Expert Comment

ID: 39465466
Hi,

First question:
What type are the amounts? If you want them exact I suggest money. (or small money)

Second question:
What was the equation used to split the 500 into 400 and 99.99?

If you want exact maths, then I suggest that you need a cursor to do so line at a time, rather than a set based approach. As in, don't assume that 4/5 of 500 is 400 and is already split. When calculating the next split figure out the proportion of what is remaining and calculate that.

For accuracy remember that a / b * c is best calculated as ( a * c ) / b (unless that causes an overload - out-of-range value)

Regards
David
0

LVL 70

Expert Comment

ID: 39465477
For now, I'll assume you can just arbitrarily add the difference -- positive or negative -- to the smallest|largest|first|last whichever-you-want in table2.

For example, this adds/subtracts it to/from the last line id:

UPDATE t2
SET
Amount = t2.Amount + (t1.Amount - t2.Total_Amount)
FROM dbo.table2 t2
INNER JOIN (
SELECT
CostID,
SUM(Amount) AS Total_Amount,
MIN(LineID) AS First_LineID,
MAX(LineID) AS Last_LineID
--,MIN(Amount) AS MinAmount
--,MAX(Amount) AS MaxAmount
FROM dbo.table2
GROUP BY CostID
) AS t2_totals ON
t2_totals.CostID = t2.CostID AND
t2.LineID = Last_LineID --change this as needed
INNER JOIN dbo.table1 t1 ON
t1.CostID = t2_totals.CostID AND
t1.Amount <> t2_totals.Total_Amount
0

LVL 39

Author Comment

ID: 39465478
Thanks David,

I'm using numeric(18,2).

The initial equation is just spreading the charges with an allocation key, i.e. line amount = total amount * line qty / sum(line qties)

I could try a cursor, but there should be way to run it set-based, no?

Thomas
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 39465488
>> but there should be way to run it set-based, no? <<

Absolutely!  No reason at all to use a cursor for this:

CORRECTION:

UPDATE t2
SET
Amount = t2.Amount + (t1.Amount - t2_totals.Total_Amount)
FROM dbo.table2 t2
INNER JOIN (
SELECT
CostID,
SUM(Amount) AS Total_Amount,
MIN(LineID) AS First_LineID,
MAX(LineID) AS Last_LineID
--,MIN(Amount) AS MinAmount
--,MAX(Amount) AS MaxAmount
FROM dbo.table2
GROUP BY CostID
) AS t2_totals ON
t2_totals.CostID = t2.CostID AND
t2.LineID = Last_LineID --change this as needed
INNER JOIN dbo.table1 t1 ON
t1.CostID = t2_totals.CostID AND
t1.Amount <> t2_totals.Total_Amount
0

LVL 70

Expert Comment

ID: 39465494
Sample run:

create table dbo.table1 (
costid int,
amount decimal(18, 2)
)
create table dbo.table2 (
costid int,
lineid int,
amount decimal(18, 2)
)

set nocount on
truncate table dbo.table1
insert into dbo.table1 values(1, 249.99)
insert into dbo.table1 values(2, 500)
insert into dbo.table1 values(3, 400)

truncate table dbo.table2
insert into dbo.table2 values(1,               1    ,           200)
insert into dbo.table2 values(1 ,              2   ,            50)
insert into dbo.table2 values(2  ,             2  ,             400.00)
insert into dbo.table2 values(2   ,            3 ,              99.99)
insert into dbo.table2 values(3    ,           1,               400)
set nocount off

select 'Before', * from dbo.table2

UPDATE t2
SET
Amount = t2.Amount + (t1.Amount - t2_totals.Total_Amount)
FROM dbo.table2 t2
INNER JOIN (
SELECT
CostID,
SUM(Amount) AS Total_Amount,
MIN(LineID) AS First_LineID,
MAX(LineID) AS Last_LineID,
MIN(Amount) AS MinAmount,
MAX(Amount) AS MaxAmount
FROM dbo.table2
GROUP BY CostID
) AS t2_totals ON
t2_totals.CostID = t2.CostID AND
t2.LineID = Last_LineID
INNER JOIN dbo.table1 t1 ON
t1.CostID = t2_totals.CostID AND
t1.Amount <> t2_totals.Total_Amount

select 'After', * from dbo.table2
0

LVL 39

Author Closing Comment

ID: 39465506
Thanks Scott, I ran it with your first example, adjusted with my actual data and got it running perfectly.

Thomas
0

LVL 35

Expert Comment

ID: 39465523
Hi,

I was suggesting a cursor to do the initial calculation.

One accounting system I wrote a report for, had an interesting rounding method, where it kept track of the individual rounds. The amount rounded (up or down) was added (or subtracted) from the next number, and then rounded. This is for financial reports where the cents aren't shown in the report. And some smart alec grabs a calculator and shows that the values as displayed don't match the total ...

So what I'm suggesting is that to absolutely avoid this kind of rounding error a similar approach should be taken ...

Scott's answer to fix this looks intriguing though. But, what is to say that in real life you'll be left with the one cent added to the \$400 and end up with

\$400.01
\$ 99.99

Regards
David
0

LVL 70

Expert Comment

ID: 39465550
>> But, what is to say that in real life you'll be left with the one cent added to the \$400 and end up with
\$400.01
\$ 99.99
<<

That's certainly possible.

To get around that, you'd have to select the row-to-adjust based on MAX(Amount * 100 % 100) or some other such mechanism to find the "furthest from .00" value.  That's one reason I wrote some flexibility into the subquery to determine the matching row value from table2.
0

LVL 39

Author Comment

ID: 39465778
In addition, for my purposes, the 400.01 doesn't bother me since I'm not expecting round values, just a matching total.

Thanks all,

Thomas
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.