Post reply

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

  • December 23, 2013 at 9:12 pm

    #286501

    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

    #1675726

    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

    #1675761

    thanks

  • December 24, 2013 at 6:52 pm

    #1675844

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 24, 2013 at 6:57 pm

    #1675845

    thanks Jeff

  • December 24, 2013 at 7:12 pm

    #1675849

    You're welcome and Happy Christmas Eve.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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