Christopher - 6 months ago 22

SQL Question

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