Prabhu Prabhu - 1 year ago 63
SQL Question

Displaying the difference between rows in the same table in SQL SERVER

I'm using SQL Server 2005.

I have a table that has an archive of rows each time some field was changed. I have to produce a report that displays fields that were changed for each employee.

My table schema:

tblEmp(empid, name, salary, createddate)

My table data:

Row 1: 1, peter, 1000, 11/4/2012
Row 2: 1, peter, 2000, 11/5/2012
Row 3: 1, pete, 2000, 11/6/2012
Row 4: 1, peter, 4000, 11/7/2012

Based on the above data for employee Peter (employee id 1), the output (changes) would be:


1, oldsalary: 1000 newsalary: 2000 (changed on 11/5/2012)
1, oldname: peter newname: pete (changed on 11/6/2012)
1, oldname: pete newname: peter, oldsalary:2000, newsalary: 4000 (changed on 11/7/2012)

I'm trying to come up with the sql that would produce the above resultset.

I've tried to do something similar to the first answer in this thread: How to get difference between two rows for a column field?

However, it's not coming together, so wondering if anyone could help.

Answer Source

Perhaps these joined CTE's with ROW_NUMBER + CASE:

WITH cte  AS 
  SELECT empid,
         rn=ROW_NUMBER()OVER(PARTITION BY empid ORDER BY createddate)
  FROM   tblemp
SELECT oldname=CASE WHEN c1.Name=c2.Name THEN '' ELSE C1.Name END,
       newname=CASE WHEN c1.Name=c2.Name THEN '' ELSE C2.Name END,
       oldsalary=CASE WHEN c1.salary=c2.salary THEN NULL ELSE C1.salary END,
       newsalary=CASE WHEN c1.salary=c2.salary THEN NULL ELSE C2.salary END
FROM cte c1 INNER JOIN cte c2 
ON c1.empid=c2.empid AND c2.RN=c1.RN + 1

Sql-Fiddle Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download