CODEWITHSUNDEEP
sql-serversql-server-2014
Kodak
Kodak

Reputation: 1829

Distribute amount to several rows in SQL Server

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?

Upvotes: 3

Views: 2970

Answers (3)

Abrar
Abrar

Reputation: 1

   -- FOR EARLIER VERSIONS OF SQL SERVER (CTE SUPPORTED)

   DECLARE @Budget INT = 905;

   IF OBJECT_ID ('TEMPDB..#emp') IS NOT NULL
   DROP TABLE #EMP

   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);

   WITH T1 AS
   ( SELECT  A.* ,
             running_total = SUM(B.Salary) -- OVER (ORDER BY [Rank] )
                                               --ROWS BETWEEN UNBOUNDED PRECEDING 
                                               --AND CURRENT ROW
                                            --)
            FROM #emp A
            LEFT JOIN #emp B ON B.[Rank] <= A.[Rank]
            GROUP BY A.Employee, A.[Rank], A.Salary
            --ORDER BY 1,2
    )

   , 
   T2 AS 
   (
    SELECT A.*, prev_running_total = B.running_total
    FROM T1 A 
    LEFT JOIN T1 B ON B.[Rank] + 1 = A.[Rank]

   )

   SELECT   Employee,
            Rank,
            Salary,
            CASE
                --run out
                WHEN isnull(prev_running_total,0) >= @Budget THEN 0
                --budget left but not enough for whole salary
                WHEN running_total > @Budget THEN @Budget - isnull(prev_running_total, 0)
                --Can do full amount 
                ELSE Salary
            END Distribution 

   FROM     T2
   ORDER BY 2;

   IF OBJECT_ID ('TEMPDB..#emp') IS NOT NULL
   DROP TABLE #EMP

Upvotes: -1

Mohit Bohra
Mohit Bohra

Reputation: 35

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453037

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

Upvotes: 7

Related Questions