Pro rata basis bonus distribution using setup table

Question

CREATE TABLE #empInfo(emp_id INT, dept_id INT, salary INT)
CREATE TABLE #empBonus(dep_id INT, emp_id INT, bonus INT) 

I have above two tables for Employee and Bonus, where I will allocate bonus for the employees in bonus table every year but in example we are going to do it for a year only so the year column is not given.

INSERT INTO #empInfo VALUES 
(111, 100, 5000),
(112, 100, 4000),
(113, 100, 4000),
(114, 100, 3500),
(115, 100, 4500),
(116, 100, 3000),
(114, 200, 3500),
(115, 200, 4500),
(116, 200, 3000),
(114, 300, 3500),
(115, 300, 3500),
(116, 300, 3500)

INSERT INTO #empBonus VALUES 
(100, 111, 1000),
(100, NULL, 4000),
(100, 111, 500),
(100, NULL, 4000),
(100, 113, 700),
(200, 114, 600),
(200, NULL, 1600),
(300, 116, 900)

Above, If employee id defined in empBonus table then the bonus should allocated for that employee and if null that means bonus for all employees whose are not listed in the empBonus and will get bonus according to their salary.

we can define bonus for multiple employees and it can be multiple for same employee, in this case we have to sum total bonus and perform operation accordingly. Same case is for NULL.

For example, Base on formula given below, I have done below calculation in the EXCEL for easy understanding and in SQL I am trying with OUTER APPLY but not getting what I want from single query ?

--Formula = bonus*salary/totSalary(of respective group or employee)
DeptID  EmpID   TotBonus    Salary      TotSalary   Bonus
100     111     1500        5000        5000        1500.00000000000
100     112     8000        4000        15000       2133.33333333333
100     113     700         4000        4000        700.00000000000
100     114     8000        3500        15000       1866.66666666666
100     115     8000        4500        15000       2400.00000000000
100     116     8000        3000        15000       1600.00000000000
200     114     600         3500        3500        600.00000000000
200     115     1600        4500        7500        960.00000000000
200     116     1600        3000        7500        640.00000000000
300     114     0           3500        7000        0.00000000000
300     115     0           3500        7000        0.00000000000
300     116     900         3500        3500        900.00000000000

Any help will be appreciated, thanks in advance :)

Answer 1

Here is one way using FULL OUTER JOIN and SUM() OVER() Window aggregate

;WITH cte
     AS (SELECT ei.emp_id,ei.dept_id, eb.dep_id,
                bonus = COALESCE(bonus, Max(CASE WHEN eb.emp_id IS NULL THEN bonus END)
                                          OVER( partition BY COALESCE(ei.dept_id, eb.dep_id) )),
                salary = Cast(salary AS NUMERIC(22, 6)),
                TotSalary= Iif(eb.emp_id IS NULL, Sum(CASE WHEN eb.emp_id IS NULL THEN salary END)
                                                    OVER(partition by ei.dept_id), salary)
         FROM   #empInfo ei
                FULL OUTER JOIN (SELECT bonus= Sum(bonus),
                                        dep_id,
                                        emp_id
                                 FROM   #empBonus
                                 GROUP  BY dep_id,
                                           emp_id) eb
                             ON ei.dept_id = eb.dep_id
                                AND eb.emp_id = ei.emp_id)
SELECT emp_id,
       bonus,
       salary,
       TotSalary,
       ( bonus * salary ) / NULLIF(TotSalary, 0)
FROM   cte
WHERE  emp_id IS NOT NULL 

Result:

+--------+-------+-------------+-----------+--------------------+
| emp_id | bonus |   salary    | TotSalary | Bonus Distribution |
+--------+-------+-------------+-----------+--------------------+
|    111 |  1500 | 5000.000000 |      5000 | 1500.00000000000   |
|    112 |  8000 | 4000.000000 |     19000 | 1684.21052631578   |
|    113 |  8000 | 4000.000000 |     19000 | 1684.21052631578   |
|    114 |  8000 | 3500.000000 |     19000 | 1473.68421052631   |
|    115 |  8000 | 4500.000000 |     19000 | 1894.73684210526   |
|    116 |  8000 | 3000.000000 |     19000 | 1263.15789473684   |
+--------+-------+-------------+-----------+--------------------+

Answer 2

Well, that was a good challenge for me.

First, create a cte is to calculate the TotSalary column:

;With cteTotalSalary as
(
    -- select total salary for employees that are in the bonus table
    SELECT e.emp_id, dept_id, Salary, Salary As TotSalary
    FROM #empInfo e
    INNER JOIN #empBonus b ON e.dept_id = b.dep_id AND e.emp_id = b.emp_id 

    UNION

    -- select total salary for employees that are in NOT the bonus table
    SELECT e.emp_id, dept_id, Salary, SUM(Salary) OVER(PARTITION BY dept_id) As TotSalary
    FROM #empInfo e
    WHERE EXISTS (
        SELECT 1
        FROM #empBonus b 
        WHERE e.dept_id = b.dep_id 
        AND b.emp_id IS NULL
    )
    AND NOT EXISTS
    (
        SELECT 1
        FROM #empBonus b 
        WHERE e.dept_id = b.dep_id 
        AND e.emp_id = b.emp_id
    )
)

Then, query this cte twice with a union to get both types of bonuses (employee bonus and department bonus)

-- Get the bonus of the employess that exists in the empBonus table
SELECT c.emp_id, dept_id, SUM(Bonus) OVER(PARTITION BY c.emp_id) as Bonus, Salary, TotSalary, CAST(SUM(CAST(Bonus as decimal)) OVER(PARTITION BY c.emp_id) as decimal) as [Bonus Distribution]
FROM cteTotalSalary c
INNER JOIN #empBonus b ON c.dept_id = b.dep_id AND c.emp_id = b.emp_id

UNION

-- Get the bonus of the employees that does not exists in the empBonus table
SELECT c.emp_id, dept_id, SUM(Bonus) OVER(PARTITION BY c.emp_id), Salary, TotSalary, SUM(CAST(Bonus as decimal) * Salary / TotSalary) OVER(PARTITION BY c.emp_id)
FROM cteTotalSalary c
INNER JOIN #empBonus b ON c.dept_id = b.dep_id AND b.emp_id IS NULL
AND NOT EXISTS (
        SELECT 1
        FROM #empBonus b 
        WHERE c.dept_id = b.dep_id 
        AND c.emp_id = b.emp_id
)

Results:

emp_id  dept_id Bonus   Salary  TotSalary   Bonus Distribution
111     100     1500    5000    5000        1500.000000000
112     100     8000    4000    19000       1684.210526314
113     100     8000    4000    19000       1684.210526314
114     100     8000    3500    19000       1473.684210526
115     100     8000    4500    19000       1894.736842104
116     100     8000    3000    19000       1263.157894736

You can see it in action here