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

sql, sql-server, sql-server-2005, t-sql

Solution

Perhaps these joined CTE's with `ROW_NUMBER` + `CASE`:

WITH cte  AS 
(
  SELECT empid,
         name,
         salary, 
         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

Problem

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: resultset: ``` 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.

Original source

Related problems