CODEWITHSUNDEEP
sqlsql-servert-sql
ChrisOPeterson
ChrisOPeterson

Reputation: 559

Sql Server Find Next Most Recent Changed Record

In my employee history table I'm trying to find what the salary was and then what it was changed to. Each Salary change inserts a new record because the new salary is considered a new "job" so it has a start and end date attached. I can select all these dates fine but I keep getting duplicates because I can't seem to compare the current record only against its most recent prior record for that employee. (if that makes sense)

I would like the results to be along the lines of:

Employe Name, OldSalary, NewSalary, ChangeDate(EndDate)
Joe           40,000     42,000     01/10/2011

Example data looks like

EmployeeHistId     EmpId     Name    Salary      StartDate     EndDate
1                  45        Joe     40,000.00   01/05/2011    01/10/2011
2                  45        Joe     42,000.00   01/11/2011    NULL
3                  46        Bob     20,000.00   01/12/2011    NULL

Upvotes: 0

Views: 1056

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

You can use the correlated join operator APPLY which can solve these types of challenges easily

select a.name, curr.salary, prev.salary, prev.enddate
from employee e
cross apply ( -- to get the current
   select top(1) *
   from emphist h
   where e.empid = h.empid  -- related to the employee
   order by startdate desc) curr
outer apply ( -- to get the prior, if any
   select top(1) *
   from emphist h
   where e.empid = h.empid  -- related to the employee
     and h.EmployeeHistId <> curr.EmployeeHistId -- prevent curr=prev
   order by enddate desc) prev  -- last ended

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294437

The Swiss army ROW_NUMBER() to the rescue:

with cte as (
select EmployeeHistId     
  , EmpId     
  , Name    
  , Salary      
  , StartDate     
  , EndDate
  , row_number () over (
      partition by EmpId order by StartDate desc) as StartDateRank
from EmployeeHist)
select n.EmpId
  , n.Name
  , o.Salary as OldDalary
  , n.Salary as NewSalary
  , o.EndData as ChangeDate
from cte n
join cte o on o.EmpId = n.EmpId 
  and n.StartDateRank = 1
  and o.StartDateRank = 2;

Use outer join to get employees that never got a raise too.

These kind of queries are always tricky because of data purity issues, if StartDate and EndDate overlap for instance.

Upvotes: 3

Chandu
Chandu

Reputation: 82943

I assume the StartDate and EndDate will be same for the new job and previous job. If thats the case try this.

SELECT a.Name AS EmployeeName, b.Salary AS NewSalary a.Salary AS NewSalary, a.StartDate AS ChangeDate
  FROM EMPLOYEE A, EMPLOYEE B
 WHERE a.EmpID = b.EmpID
   AND a.EndDate IS NULL
   AND a.StartDate = b.EndDate

Upvotes: 1

Related Questions