Christopher Christopher - 2 months ago 6
SQL Question

SQL - Using Sum command multiplies the salary for the same person

I'm trying to increase the salary for those employees who treat at least 2 patients by 10%. My problem is that the salary first multiplies by 2 for every patient they treat and then multiplies by 10% at the end. For exampel if the employee earns 25.000 and treats 3 people the new salary becomes 82.500.

select distinct t.empNbr, e.Salary, sum(e.Salary*1.1) as NewSalary from Treats t
inner join Employee e
on e.empNbr=t.empNbr
WHERE t.empNbr IN
(
SELECT empNbr
FROM Treats
GROUP BY empNbr
HAVING COUNT(*) >= 2)
group by t.empNbr, e.Salary

Answer

CROSS APPLY should help:

SELECT  e.empNbr,
        e.Salary, 
        e.Salary*1.1 as NewSalary
FROM Employee e
CROSS APPLY (
    SELECT  empNbr
    FROM Treats
    WHERE e.empNbr = empNbr
    GROUP BY empNbr
    HAVING COUNT(*) > 1
    ) as t

The t part gets empNbr we need. Then we select empNbr and salary from Employee table and do math :)

One more way:

SELECT  TOP 1 WITH TIES 
                        e.empNbr,
                        e.Salary, 
                        e.Salary*1.1 as NewSalary
FROM Employee e
INNER JOIN Treats t
    ON e.empNbr = t.empNbr
ORDER BY 
    CASE WHEN COUNT(t.empNbr) OVER (PARTITION BY t.empNbr ORDER BY t.empNbr) > 1 THEN 1 ELSE 0 END DESC,
        ROW_NUMBER() OVER (PARTITION BY t.empNbr ORDER BY t.empNbr)