SQL Help: Calculating salary change for employees

[timswim78]

Hi! I am trying to calculate the difference in salaries, from one year to the next for every employee.

For the sake of simplicity, I have a table that takes a snapshot of employee salaries once a year. It has these fields:
ID, SALARY, YEAR

I want to use SQL to create a new table that contains each employees ID and the difference in their salary between the 2011 snapshot and the 2012 snapshot. It simply needs to contain:
ID, SALCHANGE

SALCHANGE will be the difference between the employees salary at the 2011 snapshot and the 2012 snapshot.

My current solution is very crude. I selected all of the id's and salaries for 2011 into one table and all of the ids and salaries for 2012 into another table. I then joined the 2011 and 2012 tables and created a new column that is the difference between the 2011 and 2012 salary values.

 

[PhatoseAlpha]

You don't need two tables for that.

Select A.ID, A.Salary as Sal2011, B.Salary as Sal2012, B.Salary-A.Salary as SalChange
from Employees A, Employees B
where A.Year = 2011 and B.Year = 2012 and A.ID = B.ID

 

[KIAman]

^ Good response. I see too many people write SQL like they are trying to work an Excel document.

Take PhatoseAlpha's response and add an "Select into" your table name set up with those columns and you're good to go.

Edit: Bonehead

 

[timswim78]

You don't need two tables for that.

Select A.ID, A.Salary as Sal2011, B.Salary as Sal2012, B.Salary-A.Salary as SalChange
from Employees A, Employees B
where A.Year = 2011 and B.Year = 2012 and A.ID = B.ID

Perfect! Thanks. I'll slap this into a loop and run it for all the years that I need.

 

[JustAnAverageGuy]

SELECT Start.EmployeeID, 
       Start.Year, 
       Start.Salary as SalaryBeginning, 
       End.Year, 
       End.Salary as SalaryEnding, 
       End.Salary-Start.Salary as SalaryChange
FROM Employees Start
LEFT JOIN Employees End on Start.EmployeeID = End.EmployeeID and (Start.Year + 1)= End.Year
WHERE Start.Year BETWEEN 2006 AND 2011

Loops should be avoided if at all possible.

 

[rifken2]

Agree with PhatoseAlpha and JustAnAverageGuy... Self join the table and use the between clause... No need for a loop and no need for a second table...

 

[MooseNSquirrel]

http://en.wikipedia.org/wiki/Slowly_changing_dimension

 

[ForumMaster]

What you want is quite standard. you want to use what is known as the "lead analytical function". exists in oracle since version 9i (circa 2001) and finally sql server is copying it in version 2012 (it took 11 years!).

basic syntax goes like this:

LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

so to explain better and give an actual working example:

SELECT e.EmployeeID,
e.Year,
e.Salary - lead(e.salary, 1) over(partition by e.year order by e.employeedid) as salary_difference
FROM Employee e;

why is this better? one select, no joins, one sort and therefor much faster.