SQL query - How to increase the salary of employees, without exceeding a certain salary limit

user_13659789 0
Viewed 465

I am trying to get a query that will allow me to increase the salary of people who earn less than 2000, but I don't want the salary increase for these people to be higher than 2000.

The table that I am using is set-up like this:

DECLARE @Employee TABLE
(
    Id INT IDENTITY(1,1) PRIMARY KEY
   ,FirstName NVARCHAR(100)
   ,Surname NVARCHAR(100)
   ,Salary MONEY
) 
INSERT @Employee (FirstName, Surname, Salary) VALUES 
('Michael', 'Barker', 2750), ('Robert', 'Morton', 1550),
('John', 'Mitchell', 1890), ('William', 'Davison', 1840),
('James', 'Houston', 1800), ('Mark', 'Parsons', 2060),
('David', 'Higgins', 1950), ('Richard', 'Frost', 1470),
('Frank', 'Herbert', 2100), ('Brian', 'Matthews', 1930)

I am also using a variable for the salary increase, that looks like this:

DECLARE @SalaryIncreaseInPercentage DECIMAL(16, 2) = 10

The best idea that I could come up with is to use a CASE statement. How do i improve the code so the newly increased salary stops at 2000?

The code I wrote to far looks like this: 

Update @Employee
SET Salary =  CASE
WHEN Salary<2000 THEN((@SalaryIncreaseInPercentage/ 100) * Salary) + Salary
ELSE Salary
END
case sql-update sql-server sql
3 Answers

You can utilise MIN and VALUES functions

DECLARE @IncreaseFactor DECIMAL(16, 2) = 10

UPDATE e SET e.Salary = (SELECT MIN(NewSalary) 
    FROM (VALUES (e.Salary * (1 + @IncreaseFactor/100)), (2000)) AS salaries(NewSalary))
FROM @Employee e
WHERE e.Salary < 2000
user_15655250

I would only modify a little on your original query: you do not want to check if the original salary is under 2000 but if the new salary is under 2000:

UPDATE @Employee
SET Salary =  CASE
    WHEN (((@SalaryIncreaseInPercentage / 100) * Salary) + Salary) < 2000 
        THEN ((@SalaryIncreaseInPercentage / 100) * Salary) + Salary
        ELSE WHEN Salary < 2000
            THEN 2000
            ELSE Salary
END
user_95130800 
UPDATE    Employee
SET       Salary =  CASE
WHEN      Salary<2000 THEN((@SalaryIncreaseInPercentage/ 100) * Salary) + Salary
ELSE      Salary
END
user_153083720
Related