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

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.

Thanks for your help,

Thomas
LVL 39
nutschAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
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
Scott PletcherSenior DBACommented:
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
nutschAuthor Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott PletcherSenior DBACommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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
nutschAuthor Commented:
Thanks Scott, I ran it with your first example, adjusted with my actual data and got it running perfectly.

Thanks for your help.

Thomas
0
David ToddSenior DBACommented:
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
Scott PletcherSenior DBACommented:
>> 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
nutschAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.