CODEWITHSUNDEEP
sqlsql-servert-sql
Techie321
Techie321

Reputation: 97

Displaying the difference between rows in the same table

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.

Upvotes: 2

Views: 758

Answers (2)

Dale K
Dale K

Reputation: 27462

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.

Upvotes: 3

ekochergin
ekochergin

Reputation: 4129

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

Upvotes: 4

Related Questions