CODEWITHSUNDEEP
sqlsql-servert-sql
Techie321
Techie321

Reputation: 97

Display 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
... ... ...

Finally reached to below solution


CREATE TABLE Employee_audit (
    emp_audit_id int,
    eid int,
  name varchar(50),
    salary int,
    department varchar(50)   
)

insert into Employee_audit (emp_audit_id, eid, name, salary,department)
values
(1, 1, 'Daniel', 1000,'ROP'),
(2, 1, 'Dani', 1000,'ROP'),
(3, 1, 'Danny', 3000,'ROP');

    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 Employee_audit
        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 Employee_audit
       union all
        ...
    )
    select * 
    from diffs 
    where oldValue <> newValue or oldValue is null 
    order by emp_audit_id, eid

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
... ... ... ...

But the problem is, the query is very slow because to track 10 fields we have to write 10 union all.

How can I more optimize the query like in a single scan how can I do it?

Upvotes: 1

Views: 131

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Here is an option that will dynamically unpivot your data without actually using Dynamic SQL.

Example

;with cte as (
Select emp_audit_id
      ,eid
      ,[key]
      ,newValue=[value]
      ,oldvalue = lag(value) over (partition by eid,[key] order by emp_audit_id)
 From Employee_audit A
 Cross Apply ( Select [key],[value] From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper  )  ) ) B
 Where [key] not in ('emp_audit_id','eid')
)
Select emp_audit_id
      ,columName = [key]
      ,oldvalue
      ,newvalue 
 From  cte
 Where newvalue<>oldvalue or oldvalue is null

Returns

emp_audit_id    columName   oldvalue    newvalue
1               department  NULL        ROP
1               name        NULL        Daniel
2               name        Daniel      Dani
3               name        Dani        Danny
1               salary      NULL        1000
3               salary      1000        3000

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would use apply:

select t.emp_audit_id, v.columnName, v.newValue,
       lag(v.newValue) over (partition by eid, columnName order by emp_audit_id) as oldValue
from some_table t cross apply
     (values ('name', t.name),
             ('salary', t.salary), 
             . . .
     ) v (columnName, newValue);

If you need to cast the values so they are strings, that goes in the values clause:

select t.emp_audit_id, v.columnName, v.newValue,
       lag(v.newValue) over (partition by eid, ColumnName order by emp_audit_id) as oldValue
from some_table t cross apply
     (values ('name', t.name),
             ('salary', cast(t.salary as varchar(255))), 
             . . .
     ) v (columnName, newValue);

Upvotes: 2

Related Questions