Stackoverflow - Pro rata basis bonus distribution using setup table, Sql Server

Language: Layout:

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

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

INSERT INTO #empBonus VALUES 
(100, 111, 1000),
(100, NULL, 4000),
(100, 111, 500), 
(100, NULL, 4000)

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

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

View schema
Execution time: 0,06 sec, rows selected: 6, rows affected: 10, absolute service time: 0,2 sec