SQL / T-SQL update colums by rank

Question

create table Employees (
    EMP_ID INT,
    SALARY INT
);
insert into Employees (EMP_ID, SALARY) values (1, 1954);
insert into Employees (EMP_ID, SALARY) values (2, 1360);
insert into Employees (EMP_ID, SALARY) values (3, 3366);
insert into Employees (EMP_ID, SALARY) values (4, 7765);
insert into Employees (EMP_ID, SALARY) values (5, 4445);
insert into Employees (EMP_ID, SALARY) values (6, 7064);
insert into Employees (EMP_ID, SALARY) values (7, 5757);
insert into Employees (EMP_ID, SALARY) values (8, 6562);
insert into Employees (EMP_ID, SALARY) values (9, 5279);
insert into Employees (EMP_ID, SALARY) values (10, 7283);
 

Second table

create table Sales (
    EMP_ID INT,
    Amount INT
);
insert into Sales (EMP_ID, Amount) values (6, 14254);
insert into Sales (EMP_ID, Amount) values (4, 16208);
insert into Sales (EMP_ID, Amount) values (4, 12818);
insert into Sales (EMP_ID, Amount) values (4, 15381);
insert into Sales (EMP_ID, Amount) values (6, 17029);
insert into Sales (EMP_ID, Amount) values (10, 19704);
insert into Sales (EMP_ID, Amount) values (9, 10030);
insert into Sales (EMP_ID, Amount) values (2, 10416);
insert into Sales (EMP_ID, Amount) values (3, 13993);
insert into Sales (EMP_ID, Amount) values (1, 18381);
insert into Sales (EMP_ID, Amount) values (5, 12855);
insert into Sales (EMP_ID, Amount) values (8, 10215);
insert into Sales (EMP_ID, Amount) values (3, 15046);
insert into Sales (EMP_ID, Amount) values (1, 17121);
insert into Sales (EMP_ID, Amount) values (6, 10556);
insert into Sales (EMP_ID, Amount) values (5, 16914);
insert into Sales (EMP_ID, Amount) values (8, 16786);
insert into Sales (EMP_ID, Amount) values (3, 12797);
insert into Sales (EMP_ID, Amount) values (8, 14005);
insert into Sales (EMP_ID, Amount) values (6, 18943);   

I need update salary for employees by conditions : rank 1 (+ 15%) rank 2 (+ 10%) rank 3 (+5 %) the ranking by average sales from other table. Only first 3 ranks need be updated So, in first step I make ranks column with Dense_Rank

SELECT e.EMP_ID,
          AVG (Amount) as 'AVG_Amount',
          DENSE_RANK () OVER (ORDER BY AVG (Amount) DESC) AS 'DR'
          FROM Employees e JOIN Sales s ON e.EMP_ID=s.EMP_ID
          GROUP BY e.EMP_ID



   EMP_ID    AVG_Amount DR       
         10        19704    1
        1          17751    2
        6          15195    3
        5          14884    4
        4          14802    5
        3          13945    6
        8          13668    7
        2          10416    8
        9          10030    9

Next step I do not understand. I think need a loop+ifs +update ...

Please help

Answer 1

You can use a case expression. It might be more efficient to pre-aggregate in a subquery, then join:

select 
    e.emp_id, 
    s.avg_amount,
    e.salary,
    d.rn,
    salary * case s.rn
        when 1 then 1.15
        when 2 then 1.1
        when 3 then 1.05
        else 1
    end as new_salary
from employees e 
inner join (
    select 
        emp_id, 
        avg(amount) avg_amount, 
        dense_rank () over (order by avg (amount) desc) as rn
    from sales
    group by emp_id
) s on s.emp_id = e.emp_id
order by d.rn

If you want an update statement:

update e
set e.salary = 
    e.salary * case s.rn
        when 1 then 1.15
        when 2 then 1.1
        when 3 then 1.05
    end
from employees e 
inner join (
    select 
        emp_id, 
        dense_rank () over (order by avg (amount) desc) as rn
    from sales
    group by emp_id
) s on s.emp_id = e.emp_id
where s.rn <= 3

Demo on DB Fiddle:

emp_id | avg_amount | salary | rn | new_salary
-----: | ---------: | -----: | -: | ---------:
    10 |      19704 |   7283 |  1 |    8375.45
     1 |      17751 |   1954 |  2 |    2149.40
     6 |      15195 |   7064 |  3 |    7417.20
     5 |      14884 |   4445 |  4 |    4445.00
     4 |      14802 |   7765 |  5 |    7765.00
     3 |      13945 |   3366 |  6 |    3366.00
     8 |      13668 |   6562 |  7 |    6562.00
     2 |      10416 |   1360 |  8 |    1360.00
     9 |      10030 |   5279 |  9 |    5279.00