Sql Query for Salary Increase

SQL query for salary increase

Your query is the way to go.

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

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

SQL query to find employee with 3 year over year salary raises?

One option uses aggregation:

select employee_id
from mytable t
group by employee_id
having max(salary) filter(where year = 2020) > max(salary) filter(where year = 2019)
   and max(salary) filter(where year = 2019) > max(salary) filter(where year = 2018)

This brings employee whose 2020 salary is greather than their 2019 salary, and whose 2019 salary is greater than their 2018 salary - which is how I understood your question.

PL/SQL CREATE PROCEDURE - Salary increase based on tenure

The calculation in the FOR loop is wrong. In the first loop iteration you are setting SALARY to zero. In the second iteration, you are setting SALARY equal to base_salary. In the third iteration you are setting SALARY to double base_salary, etc. Also, in PL/SQL, FOR loop limits are inclusive. Hence your loop should start at 1 (one) and not 0 (zero).

The below code calculates the salary assuming that the increase is based on the current salary and not the base salary. Changes to your code are indicated by comments at the end of the changed line.

CREATE OR REPLACE PROCEDURE calculate_salary(EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE) AS
    increase FLOAT := 1.05;
    base_salary NUMBER := 10000;
    TENURE NUMBER;
    SALARY NUMBER;
    EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
    FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
    LAST_NAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
    SELECT EMPLOYEE_ID, ROUND((SYSDATE - HIRE_DATE)/365,0), FIRST_NAME, LAST_NAME INTO EMP_ID,TENURE, FIRST_NAME, LAST_NAME 
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = EMP_ID;
    SALARY := base_salary; -- Added this line.
    FOR i IN 1..TENURE LOOP -- Changed this line.
        SALARY := SALARY * increase; -- Changed this line.
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ('First Name: '||FIRST_NAME);
    DBMS_OUTPUT.PUT_LINE ('Last Name: '||LAST_NAME);
    DBMS_OUTPUT.PUT_LINE ('Salary: '||TO_CHAR(SALARY,'$99,999.99'));
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        DBMS_OUTPUT.PUT_LINE ('No Data Found!');
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE ('Error!');
END;

Increase each number value by 15% in MSSQL

UPDATE Emp
SET salary = salary * 1.15
WHERE Name = 'Zangiv'

Increase all employees' salaries for a classification by 5.65% percent, others by 4.93% percent

As @Christopher Trevor said, it should be something like this:

UPDATE e 
  SET e.Salary = 
        case when job.Job_Classification = 'Retail Sales' 
             then e.Salary * 0.0565 
             else e.Salary * 0.0493 
         end
from Employee e
inner join Job job on job.Job_ID = e.Job_ID

I have not tested this because you haven't posted the schema for the tables. But it should point you in the right direction.

How to determine an Increase in Employee Salary from consecutive Contract Rows?

Unfortunately 2008R2 doesn't have access to LAG, but you can simulate the effect of obtaining the previous row (prev) in the scope of a current row (cur), with a RANKing and a self join to the previous ranked row, in the same partition by Staff_ID):

With CTE AS 
(
    SELECT [ContractID], [Staff_ID], [EffectDate], [End Date], [Salary],[active], 
    ROW_NUMBER() OVER (Partition BY Staff_ID ORDER BY ContractID) AS Rnk
    FROM Table1
)
SELECT cur.[ContractID], cur.[Staff_ID], cur.[EffectDate], cur.[End Date],
    cur.[Salary], cur.Rnk,
    CASE WHEN (cur.Rnk = 1) THEN 0 -- i.e. baseline salary
         ELSE cur.Salary - prev.Salary END AS Increase
FROM CTE cur
    LEFT OUTER JOIN CTE prev
    ON cur.[Staff_ID] = prev.Staff_ID and cur.Rnk - 1 = prev.Rnk;

(If ContractId is always perfectly incrementing, we wouldn't need the ROW_NUMBER and could join on incrementing ContractIds, I didn't want to make this assumption).

SqlFiddle here

Edit

If you have Sql 2012 and later, the LEAD and LAG Analytic Functions make this kind of query much simpler:

SELECT [ContractID], [Staff_ID], [EffectDate], [End Date], [Salary], 
Salary - LAG(Salary, 1, Salary) OVER (Partition BY Staff_ID ORDER BY ContractID) AS Incr
FROM Table1

Updated SqlFiddle

One trick here is that we are calculating delta increments in salary, so for the first employee contract we need to return the current salary so that Salary - Salary = 0 for the first increase.

update salary by 10% by number of dependents oracle

Like this:

UPDATE Employee e
SET Salary = Salary * POWER(1.1, (SELECT COUNT(*) FROM Dependent WHERE ENumber = e.ENumber));

This:

SELECT COUNT(*) FROM Dependent WHERE ENumber = e.ENumber

returns a number say p of dependents of the employee,

so you need to raise 1.1 to the power of p and multiply by salary.

---

Related Topics