Reducing values in one table until reserves depleted in another - recursion?

Question

Hello everyone,

I have two tables - let's call them dbo.ValuesToReduce and dbo.Reserve The data in the first table (dbo.ValuesToReduce) is:

ValuesToReduceId | PartnerId | Value
-------------------------------------
1                | 1         | 53.15
2                | 2         | 601.98
3                | 1         | 91.05
4                | 2         | 44.56
5                | 3         | 19.11

The second table (dbo.Reserve) looks like this

ReserveId | PartnerId | Value
-------------------------------
1         | 1         | -101.55
2         | 2         | -425.19
3         | 3         | -28.17

What I need to do is: update the Values in ValuesToReduce table using the latter table of Reserves, reducing the numbers until the reserve supply is exhausted. Here's what I should get after running the script:

ValuesToReduceId | PartnerId | Value
-------------------------------------
1                | 1         | 0.00
2                | 2         | 176.79
3                | 1         | 42.65
4                | 2         | 44.56
5                | 3         | 0.00


ReserveId | PartnerId | Value
-------------------------------
1         | 1         | 0.00
2         | 2         | 0.00
3         | 3         | -9.06

So basically, every partner has a "reserve" which he can deplete, and values in the value table should be reduced by partner accordingly if there is still something in the reserves. Reserves should be collocated in the order provided by ValuesToReduceId.

For partner with PartnerId of 1, you can see that he had enough reserve to update his first value to 0 and still had some left to reduce the second value by that amount.

Partner with ID of 2 had a reserve of 425.19, and there were two entries in the values table for that partner, 601.98 and 44.56, in that order (by ValuesToReduceId), so we only updated the first value since the reserve is not big enough for both. The wrong way would have been to update the second value to 0.00 and the first to 221.35.

Partner with ID of 3 has more than enough reserve, so after updating his value to 0, he's left with -9.06

I tried something with recursive cte, but I can't seem to get my head around it. Hope I described the problem clearly enough..

Answer 1

You cannot, as far as I know, update two tables in a single select statement.

But you could do this in SQL using a WHILE loop. Search for the first transaction, then carry it out, until there are no possible transactions left.

declare @valid int
declare @resid int
declare @val float
while 1 = 1
    begin

    select top 1 
      @resid = r.ReserveId
    , @valid = v.ValuesToReduceId
    , @val = CASE WHEN -r.Value > v.Value THEN v.Value ELSE -r.Value END
    from ValuesToReduce v
    inner join Reserves r on r.PartnerId = v.PartnerId
    where r.Value < 0 and v.Value > 0
    order by r.ReserveId

    if @@rowcount = 0
     break

    update ValuesToReduce 
    set Value = Value - @val 
    where ValuesToReduceId = @valid

    update Reserves 
    set Value = Value + @val 
    where ReserveId = @resid
    end

Here's code to create test tables:

create table ValuesToReduce (
    ValuesToReduceId int,
    PartnerId int,
    Value float
)

insert into ValuesToReduce values (1,1,53.15)
insert into ValuesToReduce values (2,2,601.98)
insert into ValuesToReduce values (3,1,91.05)
insert into ValuesToReduce values (4,2,44.56)
insert into ValuesToReduce values (5,3,19.11)

create table Reserves (
    ReserveId int,
    PartnerId int,
    Value float
)

insert into Reserves values  (1,1,-101.55)
insert into Reserves values (2,2,-425.19)
insert into Reserves values (3,3,-28.17)