SQL Server - Set Based Waterfall

Question

I have a table that stores numeric balances in different buckets (bucket 1, 2 and 3). When an amount is posted in the system, the amount must be allocated to the buckets. The transaction type determines how it affects the buckets. Type 1, 2 and 3 increase the respective bucket (no waterfall) while type 0 decreases the buckets using a waterfall. This means the type 0 amount first reduces bucket 1, then any leftover reduces bucket 2 and any leftover reduces bucket 3.

I currently apply the amounts with a cursor that loops through each amount posted. Because of the waterfall, the sequence of the amounts being applied is key. Obviously when processing hundreds of thousands of amounts, it's quite slow. I'm wondering if there's a set based way to do this?

Code to generate tables and records:

http://pastebin.com/XgKrKkbm

My cursor that allocates the amounts is as follows:

DECLARE @Instrument int,
        @Type int,
        @Amount numeric(19,2),
        @NewAmount numeric(19,2),
        @Seq int,
        @Bucket1 numeric(19,2),
        @Bucket2 numeric(19,2),
        @Bucket3 numeric(19,2)

DECLARE Waterfall CURSOR Fast_Forward FOR 
        SELECT InstrumentID, TypeID, Amount, Sequence
        FROM Amount
        WHERE EffectiveDate between '2015-06-20' and '2015-06-30'
        ORDER BY InstrumentID, Sequence

OPEN Waterfall;
FETCH NEXT from Waterfall into @Instrument, @Type, @Amount, @Seq

WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT @Bucket1 = Bucket1, @Bucket2 = Bucket2, @Bucket3 = Bucket3
    FROM Buckets WHERE InstrumentID = @Instrument

        IF @Type > 0 /*Increase Buckets, no waterfall*/
        BEGIN
            SET @Bucket1 = CASE WHEN @Type = 1 THEN @Bucket1 + @Amount ELSE @Bucket1 END
            SET @Bucket2 = CASE WHEN @Type = 2 THEN @Bucket2 + @Amount ELSE @Bucket2 END
            SET @Bucket3 = CASE WHEN @Type = 3 THEN @Bucket3 + @Amount ELSE @Bucket3 END
        END

        ELSE /*Decrease buckets with waterfall*/
        BEGIN

            SET @NewAmount  = CASE WHEN @Amount >= @Bucket1 THEN @Amount - @Bucket1 ELSE 0 END
            SET @Bucket1    = CASE WHEN @Amount >= @Bucket1 THEN 0 ELSE @Bucket1 - @Amount END
            SET @Amount     = @NewAmount
            SET @NewAmount  = CASE WHEN @Amount >= @Bucket2 THEN @Amount - @Bucket2 ELSE 0 END
            SET @Bucket2    = CASE WHEN @Amount >= @Bucket2 THEN 0 ELSE @Bucket2 - @Amount END
            SET @Amount     = @NewAmount
            SET @Bucket3    = CASE WHEN @Amount >= @Bucket3 THEN 0 ELSE @Bucket3 - @Amount END
        END

/*Record effect of each amount on the balances for audit/undo*/
    UPDATE  Amount
    SET     Bucket1 = @Bucket1 - Buckets.Bucket1, 
            Bucket2 = @Bucket2 - Buckets.Bucket2, 
            Bucket3 = @Bucket3 - Buckets.Bucket3
    FROM    Buckets 
        inner join Amount 
            on Amount.InstrumentID = Buckets.InstrumentID
    where Sequence = @Seq

/*update bucket values in table*/    
    UPDATE Buckets
        SET Bucket1 = @Bucket1,
            Bucket2 = @Bucket2,
            Bucket3 = @Bucket3
    WHERE InstrumentID = @Instrument

FETCH NEXT from Waterfall INTO @Instrument, @Type, @Amount, @Seq
END

CLOSE Waterfall
DEALLOCATE Waterfall

Create and Insert scripts are at PasteBin as SO wouldn't let me post the question when they were included (text length?).

---

Example for clarity around waterfall sequence being critical:

Start with the following:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     500 |     200 |     3000

Two amounts to be applied as follows

Instrument | Sequence|    Type |   Amount
1          |       1 |       0 |     800  /*this decreases buckets via waterfall*/
1          |       2 |       1 |     500  /*this increases bucket 1*/

If the amounts are applied sequence, the outcome is as follows:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     500 |       0 |     2900

However the amounts are applied them in reverse order, the outcome is incorrect:

Instrument | Bucket1 | Bucket2 | Bucket 3
1          |     200 |     200 |     3000

Answer 1

Something like this is what you want. Much more efficient than a CURSOR.

UPDATE
  b
SET
  Bucket1 = CASE WHEN a.[Type] = 1 THEN b.Bucket1 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 THEN 0
                 WHEN a.[Type] = 0 AND Amount <  b.Bucket1 THEN b.Bucket1 - a.Amount
                 ELSE Bucket1  
            END,
  Bucket2 = CASE WHEN a.[Type] = 2 THEN b.Bucket2 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 + Bucket2 THEN 0
                  WHEN a.[Type] = 0 AND Amount BETWEEN  b.Bucket1 AND Bucket2 THEN Bucket2 - (a.Amount - b.Bucket1)
                 ELSE Bucket2
            END,
  Bucket3 = CASE WHEN a.[Type] = 3 THEN b.Bucket3 + a.Amount
                 WHEN a.[Type] = 0 AND Amount >= b.Bucket1 + Bucket2 THEN Bucket3  - (a.Amount - b.Bucket1 -  Bucket2)
                 ELSE Bucket3
            END
FROM
  dbo.Buckets AS b
  JOIN 
  (SELECT TOP 10000000 * FROM dbo.Amounts ORDER BY Sequence) AS a
    ON b.InstrumentID = a.InstrumentID