Displaying the difference between rows in the same table in SQL SERVER
Solution 1
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
Solution 2
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
Solution 3
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
Prabhu
Updated on June 28, 2022Comments
-
Prabhu almost 2 years
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.