CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Prabhu
Prabhu

Reputation: 13325

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:

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.

Upvotes: 3

Views: 14952

Answers (4)

Luigi68mx
Luigi68mx

Reputation: 1

Based on what you explain, It would be easier to create a Trigger when this table is Changed and then create the table with the result you expect, Since you have in that moment the old values and the New values, there should be not a problem to come up with the result you expect.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You are looking at the difference column by column. This suggests using unpivot. The following creates output with each change in a column, along with the previous value and date:

DECLARE @t TABLE(empid INT,name SYSNAME,salary INT,createddate DATE);

INSERT @t SELECT 1, 'peter', 1000, '20121104'
UNION ALL SELECT 1, 'peter', 2000, '20121105'
UNION ALL SELECT 1, 'pete',  2000, '20121106'
UNION ALL SELECT 1, 'peter', 4000, '20121107';


with cv as (
      select empid, createddate, col, val
      from (select empid, CAST(name as varchar(8000)) as name,
                   CAST(salary as varchar(8000)) as salary, createddate
            from @t
           ) t
      unpivot (val for col in (name, salary)) as unpvt
     ),
    cvr as (
     select cv.*,
            ROW_NUMBER() over (partition by empid, col order by createddate) as seqnum_all
     from (select cv.*, ROW_NUMBER() over (partition by empid, col, thegroup order by createddate) as seqnum_group
           from (select cv.*,
                        (ROW_NUMBER() over (partition by empid, col order by createddate) -
                         ROW_NUMBER() over (partition by empid, col, val order by createddate)
                        ) as thegroup
                 from cv
                ) cv
          ) cv
     where seqnum_group = 1
    ) -- select * from cvr
select cvr.*, cvrprev.val as preval, cvrprev.createddate as prevdate
from cvr left outer join
     cvr cvrprev
     on cvr.empid = cvrprev.empid and
        cvr.col = cvrprev.col and
        cvr.seqnum_all = cvrprev.seqnum_all + 1

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460028

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

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

DECLARE @t TABLE(empid INT,name SYSNAME,salary INT,createddate DATE);

INSERT @t SELECT 1, 'peter', 1000, '20121104'
UNION ALL SELECT 1, 'peter', 2000, '20121105'
UNION ALL SELECT 1, 'pete',  2000, '20121106'
UNION ALL SELECT 1, 'peter', 4000, '20121107';

;WITH x AS
(
  SELECT empid, name, salary, createddate, rn = ROW_NUMBER() OVER 
  (PARTITION BY empid ORDER BY createddate)
  FROM @t
  -- WHERE empid = 1 -- for example
)
SELECT LTRIM(
  CASE WHEN x.salary <> y.salary THEN 
    'oldsalary: ' + RTRIM(x.salary)
    + ' newsalary: ' + RTRIM(y.salary)
  ELSE '' END
  + CASE WHEN x.name <> y.name THEN 
    ' oldname: ' + x.name
    + ' newname: ' + y.name
  ELSE '' END
  + ' (changed on ' + CONVERT(CHAR(10), y.createddate, 101) + ')')
FROM x INNER JOIN x AS y
ON x.rn = y.rn - 1
AND x.empid = y.empid
AND
(
 x.salary <> y.salary 
 OR x.name <> y.name
);

Unless you have a where clause to target a specific empid, however, the output is not very useful unless it also includes empid. SQLfiddle demo

Upvotes: 2

Related Questions