Output old/new values of a column from an audit table

Question

Post reply

Output old/new values of a column from an audit table

hwells

Right there with Babe

Points: 732
More actions
May 24, 2011 at 3:13 pm

#238081

A third party vendor has given us read access to an audit table, allowing us to review historical changes for a given person. In this case we wish to show email address changes. Here is some sample data.
if OBJECT_ID('TempDB..#audit_Person','U') IS NOT NULL
drop table #audit_Person
create table #audit_Person
(
ind_id int,
first_name varchar(10),
last_name varchar(10),
email_addr varchar(255),
created_dt datetime,
updated_dt datetime
)
Insert into #audit_Person (ind_id, first_name, last_name, email_addr, created_dt, updated_dt)
SELECT 1, 'Joe', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117','2010-10-19 15:24:53.253' UNION ALL
SELECT 1, 'Joseph', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117', '2010-12-02 11:23:09.430' UNION ALL
SELECT 1, 'Josephina', 'Smith', 'nobody@nowhere.com', '2010-02-27 17:40:03.117', '2010-12-31 11:59:59.055' UNION ALL
SELECT 1, 'Joseph', 'Smith', 'somebody@somebody.com', '2010-02-27 17:40:03.117', '2011-05-02 12:09:34.443'
The desired output:
[font="Courier New"]ind_id Old_Email_Addr New_Email_Addr updated_dt
----------- ------------------------- ------------------------- -----------------------
1 nobody@nobody.com nobody@nowhere.com 2010-12-31 11:59:59.057
1 nobody@nowhere.com somebody@somebody.com 2011-05-02 12:09:34.443[/font]
The actual table contains approximately 2.3 million rows, hence my interest in a fast performing set based solution for this. I have played with ROW_NUMBER() but it isn't exactly correct.
Any help is appreciated!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

How's this? (comments in the code)

;

WITH cte AS

(

-- get the columns we actually need

-- add a row number that restarts whenever the ind_id changes

-- order the row number by the updated_dt

SELECT ind_id,

email_addr,

updated_dt,

RN = ROW_NUMBER() OVER (PARTITION BY ind_id ORDER BY updated_dt)

FROM #audit_Person p

)

-- get the records where the email_addr has changed

-- show the old/new email addr, and the date changed

SELECT c1.ind_id,

Old_Email_Addr = c1.email_addr,

New_Email_Addr = c2.email_addr,

c2.updated_dt

FROM cte c1

LEFT JOIN cte c2

ON c1.ind_id = c2.ind_id -- same ind_id

AND c1.RN = c2.RN - 1 -- next change

WHERE c1.email_addr <> c2.email_addr -- email_addr changed

ORDER BY c1.ind_id, c1.RN;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

hwells Right there with Babe Points: 732 More actions · Answer 2

Great solution, it works well. It's taking me a bit to thoroughly understand how the join on c2.RN-1 works. At first I thought that would be an issue where no update to the email was made. I am assuming because a CTE is used the non-email change rows are filtered in the same pass when the row number is created. I need to do more reading on how SQL Server logically processes a select statement.

Thanks for the help!