Detect Salary hike % for each employee

HR need to compare each employee salary with the previous year salary and also show the salary difference (Increment or decrement) with salary hike %. Here our goal to get the Previous year salary, Current year salary and Salary hike percentage for each employee, over time.

Here we will use LAG with PARTITION BY employee and ORDER BY effective date to get previous salary, then calculate hike percentage using a window function.

For this demo we will create a employee table and inserting few records.

CREATE TABLE EmployeeSalary (     EmpID INT,     EmpName VARCHAR(50),     Salary INT,     EffectiveDate DATE );  --inserting few records INSERT INTO EmployeeSalary VALUES (1, 'Amit', 40000, '2022-01-01'), (1, 'Amit', 45000, '2023-01-01'), (1, 'Amit', 50000, '2024-01-01'), (2, 'Neha', 60000, '2022-02-01'), (2, 'Neha', 66000, '2023-02-01'), (2, 'Neha', 72600, '2024-02-01'), (3, 'Bagesh', 45000, '2022-01-01'), (3, 'Bagesh', 48000, '2023-01-01'), (3, 'Bagesh', 50000, '2024-01-01'), (4, 'Mohit', 30000, '2022-02-01'), (4, 'Mohit', 36000, '2023-02-01'), (4, 'Mohit', 42600, '2024-02-01'), (5, 'Abhiraj', 10000, '2022-01-01'), (5, 'Abhiraj', 15000, '2023-01-01'), (5, 'Abhiraj', 20000, '2024-01-01'), (6, 'Nagesh', 50000, '2022-02-01'), (6, 'Nagesh', 56000, '2023-02-01'), (6, 'Nagesh', 62600, '2024-02-01');

 See the records in this table

Here we will use LAG function. LAG lets us access the previous row’s salary within the same employee partition.

Below query is used to calculate the Hike %

Hike % - (Current − Previous) × 100 / Previous

WITH cte_salary      AS (SELECT empid,                 empname,                 salary                       AS CurrentSalary,                 Lag(salary)                   OVER (                     partition BY empid                     ORDER BY effectivedate ) AS PreviousSalary,                 effectivedate          FROM   employeesalary) SELECT empid,        empname,        effectivedate,        previoussalary,        currentsalary,        ( ( currentsalary - Isnull(previoussalary, currentsalary) ) * 100.0 ) /        Isnull(        previoussalary, currentsalary) AS [Hike %] FROM   cte_salary

 

For the first record of each employee, previous salary will be NULL — which makes sense.