Displaying the difference between rows in the same table

Question

I have a table named Employee_audit with following schema,

emp_audit_id	eid	name	salary
1	1	Daniel	1000
2	1	Dani	1000
3	1	Danny	3000

My goal is to write a SQL query which will return in following format, considering the first row also as changed value from null.

columnName	oldValue	newValue
name	null	Daniel
salary	null	1000
name	Daniel	Dani
name	Dani	Danny
salary	1000	3000

I have written the below SQL query,

WITH cte  AS 
(
  SELECT empid,
         name,
         salary, 
         rn=ROW_NUMBER()OVER(PARTITION BY empid ORDER BY emp_audit_id)
  FROM   Employee_audit 
) 
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

But it gives the result in following format

oldname	newname	oldsalary	newsalary
Daniel	Dani	null	null
Dani	Danny	1000	3000

Could you please answer me, how can I get the required result.

user_6033601 · Answer

The lead and lag functions are to help you out.

The "diffs" calculates differences for each column you need to find diff to

with diffs as (
    select 'name' colName, emp_audit_id, eid, lag(name, 1, null) over (partition by eid order by emp_audit_id) oldValue, name newValue
    from some_table
    union all
    select 'salary', emp_audit_id, eid, cast(lag(salary, 1, null) over (partition by eid order by emp_audit_id) as varchar), cast(salary as varchar) newValue
    from some_table
)
select * 
from diffs 
where oldValue <> newValue or oldValue is null 
order by emp_audit_id, eid

user_1127428 · Answer

If you give each row a row number in a CTE then join on yourself to the next row you can compare the old and the new values. Unioning the 2 different column names is a bit clunky however, if you needed a more robust solution you might look at pivoting the data.

You also obviously have to convert all values to a common datatype e.g. a string.

declare @Test table (emp_audit_id int, eid int, [name] varchar(32), salary money);

insert into @Test (emp_audit_id, eid, [name], salary)
values
(1, 1, 'Daniel', 1000),
(2, 1, 'Dani', 1000),
(3, 1, 'Danny', 3000);

with cte as (
    select emp_audit_id, eid, [name], salary
      , row_number() over (partition by eid order by emp_audit_id) rn
    from @Test
)
select C.emp_audit_id, 'name' columnName, P.[Name] oldValue, C.[name] newValue
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.[name],'') != coalesce(P.[Name],'')
union all
select C.emp_audit_id, 'salary' columnName, convert(varchar(21),P.salary), convert(varchar(21),C.salary)
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.salary,0) != coalesce(P.salary,0)
order by C.emp_audit_id, columnName;

Returns:

emp_audit_id	columnName	oldValue	newValue
1	name	NULL	Daniel
1	salary	NULL	1000.00
2	name	Daniel	Dani
3	name	Dani	Danny
3	salary	1000.00	3000.00

I highly encourage you to add DDL+DML (as show above) to all your future questions as it makes it much easier for people to assist.

Displaying the difference between rows in the same table

2 Answers