December 23, 2013 at 9:12 pm
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 id and login changes as old and new rows. Here is some sample data.
if OBJECT_ID('TempDB..#audit_Person','U') IS NOT NULL
drop table #audit_Person
create table #audit_Person
(
old_ind_id int,
new_ind_id int,
old_login int,
new_login int,
first_name varchar(10),
last_name varchar(10),
email_addr varchar(255),
created_dt datetime,
updated_dt datetime
)
Insert into #audit_Person (old_ind_id,new_ind_id,old_login, new_login, first_name, last_name, email_addr, created_dt, updated_dt)
SELECT 1, 3, 567, 568, 'Joe', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117','2010-10-19 15:24:53.253' UNION ALL
SELECT 4, 7, 123, 223,'Joseph', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117', '2010-12-02 11:23:09.430' UNION ALL
SELECT 5, 8, 112, 334, 'Josephina', 'Smith', 'nobody@nowhere.com', '2010-02-27 17:40:03.117', '2010-12-31 11:59:59.055' UNION ALL
SELECT 6, 9, 667 , 778, 'Joseph', 'Smith', 'somebody@somebody.com', '2010-02-27 17:40:03.117', '2011-05-02 12:09:34.443'
select * from #audit_Person
The desired output:
old 1 567 'Joe', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117','2010-10-19 15:24:53.253'
new 3 568 'Joe', 'Smith', 'nobody@nobody.com', '2010-02-27 17:40:03.117','2010-10-19 15:24:53.253'
old 4
new 7..................
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
December 23, 2013 at 10:59 pm
Try this:
;with cte as
(
select *, ROW_NUMBER() over(order by old_ind_id) as RowNum
from #audit_Person
)
select 'old', old_ind_id, old_login, first_name, last_name, email_addr, created_dt, updated_dt,RowNum
from cte
union
select 'new', new_ind_id, new_login, first_name, last_name, email_addr, created_dt, updated_dt,RowNum
from cte
order by RowNum, 1 desc
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 24, 2013 at 6:00 am
thanks
December 24, 2013 at 6:52 pm
LinksUp (12/23/2013)
Try this:
;with cte as
(
select *, ROW_NUMBER() over(order by old_ind_id) as RowNum
from #audit_Person
)
select 'old', old_ind_id, old_login, first_name, last_name, email_addr, created_dt, updated_dt,RowNum
from cte
union
select 'new', new_ind_id, new_login, first_name, last_name, email_addr, created_dt, updated_dt,RowNum
from cte
order by RowNum, 1 desc
"Teach a man to fish"...
That works just fine for the given output but be advised that CTEs work just like views... every time you reference one, the whole CTE is executed and that includes dips on the underlying tables. Your good code makes 2 dips on the tables, has to do a RowNum calculation, and a sort based on that calculation. That can get expensive pretty quickly.
With the understanding that ALL of the data for 2 rows is contained on each row, explore how the following works to eliminate one of the table scans and all sorts. Compare execution plans to see.
SELECT ca.*
FROM #audit_Person ap
CROSS APPLY
(
SELECT 'old', old_ind_id, old_login, first_name, last_name, email_addr, created_dt, updated_dt UNION ALL
SELECT 'new', new_ind_id, new_login, first_name, last_name, email_addr, created_dt, updated_dt
) ca (RowType, ind_id, login, first_name, last_name, email_addr, created_dt, updated_dt)
;
In this case, CROSS APPLY is being used as an UNPIVOT and it's nasty fast.
--Jeff Moden
December 24, 2013 at 6:57 pm
thanks Jeff
December 24, 2013 at 7:12 pm
You're welcome and Happy Christmas Eve.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply