Distribute amount to several rows in SQL Server

Question

I got arbitrary amount to spend, let's say 1000$

I also got a dozens of rows, let's say employees with their salary as a column

How can I distribute a budget of 1000$ among the employees in priority order of rank so they each get the value in the salary column until the money is all spent? The rest of the employees once the budget is all spent would then be left with zero.

Employee, Rank, Salary
John, 1, 500$
Anne, 2, 400$
Rob, 3, 300$
Bill, 4, 200$

The result should be:

John, 1, 500$, 500$
Anne, 2, 400$, 400$
Rob, 3, 300$, 100$    --Only 100 left in the budget
Bill, 4, 200$, 0$

Any idea how to do it without a cursor?

Answer 1

Here's one way.

CREATE TABLE #emp (Employee VARCHAR(10), Rank INT, Salary INT CHECK (Salary > 0));

INSERT INTO #emp
VALUES      ('John',1,500),
            ('Anne',2,400),
            ('Rob',3,300 ),
            ('Bill',4,200);

DECLARE @Budget INT = 1000;

WITH T1 AS
( SELECT  * ,
          running_total = SUM(Salary) OVER (ORDER BY Rank 
                                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                            AND CURRENT ROW)
         FROM #emp ), 
T2 AS 
(
SELECT *, 
      prev_running_total = LAG(running_total) OVER (ORDER BY Rank)
FROM T1
)
SELECT   Employee,
         Rank,
         Salary,
         CASE
             --run out
             WHEN prev_running_total >= @Budget THEN 0
             --budget left but not enough for whole salary
             WHEN running_total > @Budget THEN @Budget - prev_running_total 
             --Can do full amount 
             ELSE Salary
         END
FROM     T2;

DROP TABLE #emp 

Answer 2

If budget is less than Salary

 CASE
             --run out
             WHEN prev_running_total >= @Budget THEN 0
             WHEN @Budget <= T2.Salary  THEN @Budget  
             --budget left but not enough for whole salary
             WHEN running_total > @Budget THEN @Budget - prev_running_total 
             --Can do full amount 
             ELSE Salary
         END