SQL Server 2012 Distribute amount based on priority and percentage

Question

Hope you can assist.

Here is my table:

Declare @AmtToDistribute float = 500.00

DECLARE @TestTable TABLE(TempID INT IDENTITY(1, 1) NOT NULL,
                         VicID INT ,
                         VicOrderedAmt MONEY,
                         RemainingBalance MONEY,
                         DistPriority INT,
                         DistPercentOf DECIMAL(5, 2),
                         DistributionAmt MONEY
                        );

INSERT INTO @TestTable ([VicID], [VicOrderedAmt], [RemainingBalance], [DistPriority], [DistPercentOf], [DistributionAmt])
VALUES (2318, 5.00, 5.00, 1, 60.00, 0),
       (2319, 50.00, 25, 1, 40.00, 0),
       (2320, 500.00, 500.0, 2, 33.00, 0),
       (2321, 500.00, 500.0, 2, 33.00, 0),
       (2322, 500.00, 500.0, 2, 34.00, 0);

SELECT * FROM @TestTable;

Sample table structure

I'm trying to find a query that allows me to distribute a dollar amount to each row in the 1st priority until those values reach zero and then jump to the next priority until those values reach zero. The catch is that the dollar amount has to be distributed based on the DistPercentOf column. For instance the 2 rows in priority 1 split the amount first with the VicID getting 60% of the amount until the VicOrderedAmt has been met, then the second VicId get 40% of the amount. After priority 1 amounts have been met, move to Priority 2.

I've seen a few posts, but they deal with just a single priority and I need to payoff one priority before moving to the next.

Thanks in advance.

Answer 1

Try something like this:

Declare @AmtToDistribute MONEY = 500.00

DECLARE @TestTable TABLE (
    TempID                   INT IDENTITY(1, 1) NOT NULL,
    VicID                    INT ,
    VicOrderedAmt            MONEY,
    RemainingBalance         MONEY,
    DistPriority             INT,
    DistPercentOf            DECIMAL(5, 2),
    DistributionAmt          MONEY
);

INSERT INTO @TestTable
    (VicID, VicOrderedAmt, RemainingBalance, DistPriority, DistPercentOf, DistributionAmt)
    VALUES (2318, 5.00, 5.00, 1, 60.00, 0)
    , (2319, 50.00, 25, 1, 40.00, 0)
    , (2320, 500.00, 500.0, 2, 33.00, 0)
    , (2321, 500.00, 500.0, 2, 33.00, 0)
    , (2322, 500.00, 500.0, 2, 34.00, 0);

WHILE 1=1 BEGIN
    UPDATE y 
    SET y.DistributionAmt=y.DistributionAmt+y.CurrentDistributedAmt, 
        y.RemainingBalance=y.RemainingBalance-y.CurrentDistributedAmt, 
        @AmtToDistribute=@AmtToDistribute-CurrentDistributedAmt
    FROM (
        SELECT *, 
            CASE WHEN @AmtToDistribute<RemainingBalanceForCurrentPriority
                THEN CASE 
                    WHEN RemainingBalance<@AmtToDistribute*x.DistPercentOf/TotalDistPercent 
                    THEN x.RemainingBalance
                    ELSE @AmtToDistribute*x.DistPercentOf/TotalDistPercent 
                END
                ELSE x.RemainingBalance
            END AS CurrentDistributedAmt
        FROM (
            SELECT *, SUM(RemainingBalance) OVER () AS RemainingBalanceForCurrentPriority,
                SUM(DistPercentOf) OVER () AS TotalDistPercent
            FROM @TestTable WHERE DistPriority=(
                SELECT MIN(DistPriority) FROM @TestTable 
                WHERE RemainingBalance>0
            ) AND RemainingBalance>0
        ) x
    ) y

    IF @AmtToDistribute=0 BREAK
    IF NOT EXISTS (SELECT * FROM @TestTable WHERE RemainingBalance>0) BREAK
END

select * From @TestTable;

To avoid rounding errors, make sure that @AmtToDistribute has the same data type as the DistributionAmt column.

Later edit (12 feb 2017):

I made a few corrections to the UPDATE statement in the code above.

Additionally, consider using NUMERIC(19,4) instead of MONEY, because it has better rounding behaviour. See Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server? for more info.