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

Question

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.

How are you determining which row is row 1, which row is row 2, etc.? There doesn't seem to be a column to indicate which row happened first, so how can SQL Server tell you? It doesn't track which rows were inserted in which order - think of a table as a bag of rows. — Aaron Bertrand, Apr 15 '13 at 21:08
The question you linked to is very similar to yours, what does "it's not coming together" actually mean? — Pondlife, Apr 15 '13 at 21:09
Assume that it's in order. Row 1 got changed to Row 2, which got changed to Row 3...etc — Prabhu, Apr 15 '13 at 21:09
@Prabhu . . . SQL tables are *not* ordered. You need to have an explicit ordering column -- a row id, creation date, or something like that. — Gordon Linoff, Apr 15 '13 at 21:11
@Pondlife, I modified it slightly so that it displays the old value and the new value instead of doing a subtraction, but new value is always NULL, which is not in this case — Prabhu, Apr 15 '13 at 21:11
@Gordon, Yes, I have a date column. Just wanted to simplify the example. Will update my question. — Prabhu, Apr 15 '13 at 21:12
FYI you'll get an accurate answer quicker if you *don't* dumb down the example and leave out crucial details. — Aaron Bertrand, Apr 15 '13 at 21:46

score 4 · Answer 1 · answered Apr 15 '13 at 21:48

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

Thanks @Gordon. While Tim's answer works for me, I'm just wondering if this one has any advantage over that. Thanks for your time. — Prabhu, Apr 15 '13 at 22:02
@Prabhu . . . This is a different way of thinking about the problem. Tim's approach is to look at adjacent rows and make comparisons. This approach looks at each column and identifies the changes. They should return similar results. — Gordon Linoff, Apr 16 '13 at 13:10

Tim Schmelter · Accepted Answer · 2013-04-15T21:33:29.800

3

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

edited Apr 15 '13 at 21:33

answered Apr 15 '13 at 21:25

Tim Schmelter

450,073
74
686
939

`ORDER BY empid` doesn't do anything useful... that's not a key, so how do you know which row gets rn = 1, rn = 2, etc.? – Aaron Bertrand Apr 15 '13 at 21:32
@AaronBertrand: Edited my answer. I haven't seen that OP has edited his answer meanwhile. – Tim Schmelter Apr 15 '13 at 21:35

Aaron Bertrand · Answer 3 · 2013-04-15T21:32:12.803

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

Thanks a lot @Aaron, this solution works for me as well. I just happened to try Tim's answer first. — Prabhu, Apr 15 '13 at 21:42

score 0 · Answer 4 · answered Jun 28 '16 at 01:30

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.

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

4 Answers4