CODEWITHSUNDEEP
sql-servert-sqlaudit
Bishan
Bishan

Reputation: 15740

Find changes from an audit table

I have data in an audit table like below. Fiddle 1, Fiddle 2

Audit Table

I need to get updated columns and data details from this table.

Expected Results is:

Employee Name : from Jone to Jhone

Employee Age : from 25 to 20

Employee Name : from Jhonny to Jone, Employee Age : from 18 to 25

How could I achieve this?

UPDATE

When a record get updated, I am inserting two rows in to audit table. First record as Old(auditDataState) with data before the update and Second record as New(auditDataState) with updated data.

So each update has two records in audit table as Old and New with old and new values.

I need to get updated data from the audit table based on updateColumns in audit table.(I am storing what columns get updated in updateColumns column).

I don't need to first row to be Employee Name : from Jone to Jhone. Just need to identify updated values.

Upvotes: 0

Views: 2121

Answers (1)

iamdave
iamdave

Reputation: 12243

This will provide the result as specified in your question, though it is far from a sensible or scalable solution. If at all possible, I would recommend you completely revisit your change auditing:

declare @EmpAudit table (
      empID int
    , empName varchar(50)
    , empAge int
    , auditDataState varchar(50)
    , auditDMLAction varchar(50)
    , auditUser varchar(50)
    , auditDateTime datetime
    , updateColumns varchar(50)
);

insert into @EmpAudit values
      (1, 'Alex', 22, 'New', 'Insert','[email protected]',getdate(),''),
      (2, 'Jhonny', 18, 'New', 'Insert','[email protected]',getdate()-0.5,''),

      (2, 'Jhonny', 18, 'Old', 'Update','[email protected]',getdate()-1,'Employee Name, Employee Age'),
      (2, 'Jone', 25, 'New', 'Update','[email protected]',getdate()-1.5,'Employee Name, Employee Age'),

      (2, 'Jone', 25, 'Old', 'Update','[email protected]',getdate()-2,'Employee Age'),
      (2, 'Jone', 30, 'New', 'Update','[email protected]',getdate()-2.5,'Employee Age'),

      (2, 'Jone', 30, 'Old', 'Update','[email protected]',getdate()-3,'Employee Age'),
      (2, 'Jone', 20, 'New', 'Update','[email protected]',getdate()-3.5,'Employee Age'),

      (2, 'Jone', 20, 'Old', 'Update','[email protected]',getdate()-4,'Employee Name'),
      (2, 'Jhone', 20, 'New', 'Update','[email protected]',getdate()-4.5,'Employee Name');

with d as
(
    select empID
            ,empName
            ,empAge
            ,auditDataState
            ,auditDMLAction
            ,auditUser
            ,auditDateTime
            ,updateColumns
            ,row_number() over (partition by empID order by auditDateTime) as rn
    from @EmpAudit
)
select case when o.empName <> n.empName then 'Employee Name : from ' + o.empName + ' to ' + n.empName else '' end
      +case when charindex(',',o.UpdateColumns) > 0 then ', ' else '' end
      +case when o.empAge <> n.empAge then 'Employee Age : from ' + cast(o.empAge as varchar(3)) + ' to ' + cast(n.empAge as varchar(3)) else '' end as Change
from d as o
    join d as n
        on o.empID = n.empID
            and o.updateColumns = n.updateColumns
            and o.rn = n.rn+1
            and n.auditDataState = 'New'
where o.auditDataState = 'Old';

Output:

Change
-----------------------------------------------------------------
Employee Name : from Jone to Jhone
Employee Age : from 30 to 20
Employee Age : from 25 to 30
Employee Name : from Jhonny to Jone, Employee Age : from 18 to 25

Upvotes: 1

Related Questions