CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Losbear
Losbear

Reputation: 3315

SQL Server query: Adjust money column

I'm trying to do the following WITHOUT CURSORS. Is it possible?

I have this variable:

DECLARE @Amount MONEY = 800;

Sample table that I current have:

EntryID    Amount
--------------------------------------------
1       200
2       250
3       600
4       100
...     ....(could be many more rows)

I want to be able to update the table to only show the "Amount" that it needs to add up to the @Amount variable ($800). So I'd like the table to end up like this:

EntryID    Amount
--------------------------------------------
1       200 (uses up full 200)
2       250 (uses up full 250)
3       350 (uses up 350 of the 600)

OR LIKE THIS

EntryID    Amount     Distrib
--------------------------------------------
1       200        200
2       250        250
3       600        350
4       100        0
...     ...            ...

So the SUM() is equal to @Amount

Thanks in advance!

PS - I'm doing this in SQL Server 2012

UPDATE:

Here's my final solution. Thanks again, Gordon:

DECLARE @Amount money = 800;
DECLARE @tmpPaymentDist TABLE (EntryID INT, Amount MONEY, Distrib MONEY);

INSERT INTO @tmpPaymentDist (EntryID, Amount) VALUES (1, 200);
INSERT INTO @tmpPaymentDist (EntryID, Amount) VALUES (2, 250);
INSERT INTO @tmpPaymentDist (EntryID, Amount) VALUES (3, 600);
INSERT INTO @tmpPaymentDist (EntryID, Amount) VALUES (4, 100);

with toupdate as (
    select t.*,
        (case when sum(amount) over (order by entryid) <= @amount
        then amount
        when sum(amount) over (order by entryid) < @amount + amount
        then @amount - (sum(amount) over (order by entryid) - amount)
        else 0
    end) as new_distrib
    from @tmpPaymentDist t
)

update T set distrib = new_distrib 
FROM @tmpPaymentDist T
INNER JOIN toupdate T2 ON T2.EntryID = T.EntryID
WHERE T2.new_distrib > 0

SELECT * FROM @tmpPaymentDist

Upvotes: 2

Views: 151

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Yes, you can do this without cursors, using cumulative sums:

select t.*,
       (case when sum(amount) over (order by entryid) <= @amount
             then amount
             when sum(amount) over (order by entryid) < @amount + amount
             then @amount - (sum(amount) over (order by entryid) - amount)
             else 0
        end) as distrib
from table t;

That is, use cumulative sums for the calculation.

For an update, you can use the same logic:

with toupdate as (
      select t.*,
             (case when sum(amount) over (order by entryid) <= @amount
                   then amount
                   when sum(amount) over (order by entryid) < @amount + amount
                   then @amount - (sum(amount) over (order by entryid) - amount)
                   else 0
              end) as new_distrib
      from table t
     )
update toudpate
    set distrib = new_distrib;

Upvotes: 3

Related Questions