How to Compare Two Rows and find out which columns got changed

  • Ganapathi M

    Say Hey Kid

    Points: 702

    Hello Experts,

    create table #tab1_audit

    (

    cls_run_id int,

    prcs_dt datetime,

    part_id int,

    prdct_id char(15),

    pay_clct_am numeric (19,9),

    pstn_type_cd char(1),

    ccy_cd char(3),

    tick_val_am numeric (19,9),

    tick_dnmtn_am numeric (19,9),

    adt_ts datetime,

    adt_user_id varchar(20),

    adt_actn_cd char(2)

    )

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 1:18 AM','APADMIN','CO')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO')

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN')

    Here last column 'audit action code' : CO means OLD Value, CN means New Value.

    How to Compare Two Rows and find which are all the columns are got changed for the each part id

    actually i have around 20 columns in the table and need to list all the columns that are got changed.

    Result set should be like

    ---------------------------------------------------------------------

    ReferenceNo Columns_Changed OLD_VALUE NEW_VALUE

    ---------------------------------------------------------------------

    1002 pay_clct_am -10800 -11880

    1002 pay_clct_am -11880 -10

    1002 ccy_cd USD INR

    1003 prdct_id VEDF201112 VEDF201111

    1003 tick_dnmtn_am 10000 20000

    ---------------------------------------------------------------------------

    Kindly help me to get this report.

    I could see some solutions in our site, which has used PIVOT table and 'XML codes', but kindly suggest me as a SQL code so that i have to implement this code in both SQL SERVER and SYBASE 15.0

    Thanks,

    Ganpat

  • LutzM

    SSC Guru

    Points: 107049

    Here's a SQL Server solution. AFAIK, CASE and CAST should work in SyBase 15.

    SELECT

    t1.part_id,

    t1.adt_ts,

    CASE

    WHEN t1.pay_clct_am = t2.pay_clct_am

    THEN 'unchanged'

    ELSE 'old:' + CAST(t1.pay_clct_am AS VARCHAR(20)) + ', new:' + CAST(t2.pay_clct_am AS VARCHAR(20))

    END AS pay_clct_am,

    CASE

    WHEN t1.ccy_cd = t2.ccy_cd

    THEN 'unchanged'

    ELSE 'old:' + CAST(t1.ccy_cd AS VARCHAR(20)) + ', new:' + CAST(t2.ccy_cd AS VARCHAR(20))

    END AS pay_clct_am

    FROM #tab1_audit t1

    INNER JOIN #tab1_audit t2

    ON t1.part_id=t2.part_id

    AND t1.adt_ts=t2.adt_ts

    AND t1.adt_actn_cd='CO'

    AND t2.adt_actn_cd='CN'

    WHERE

    (

    t1.pay_clct_am <> t2.pay_clct_am

    OR t1.ccy_cd <> t2.ccy_cd

    OR t1.tick_val_am <> t2.tick_val_am

    OR t1.pay_clct_am <> t2.pay_clct_am

    OR t1.tick_dnmtn_am <> t2.tick_dnmtn_am

    )



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Ganapathi M

    Say Hey Kid

    Points: 702

    Hi LutzM,

    Thanks for your solution.

    Now we are getting the result as follows:

    ----------------------------------------------------------------------------------

    part_id adt_ts pay_clct_am ccy_cd

    ----------------------------------------------------------------------------------

    1002 4/8/11 10:29 AM old:-10800.000000000, new:-11880.000000000 unchanged

    1002 4/8/11 11:04 PM old:-11880.000000000, new:-10.000000000 old:USD, new:INR

    1003 4/8/11 11:04 PM unchanged unchanged

    ---------------------------------------------------------------------------------

    Actually, (i) we no need to display the columns value which are not modified.

    (2) the old value should display under column name OLD_VALUE,

    (3) new value should display under column name NEW_VALUE,

    (4) Modified column names should display under COLUMNS_CHANGED

    Our Result set should be like below:

    -------------------------------------------------------------------------

    Part_ID Columns_Changed OLD_VALUE NEW_VALUE

    -------------------------------------------------------------------------

    1002 pay_clct_am -10800 -11880

    1002 pay_clct_am -11880 -10

    1002 ccy_cd USD INR

    1003 prdct_id VEDF201112 VEDF201111

    1003 tick_dnmtn_am 10000 20000

    -------------------------------------------------------------------------

    Thanks for spending your time.

  • LutzM

    SSC Guru

    Points: 107049

    I would use the UNPIVOT approach followed by a self join.

    This would cover items 1 to 3.

    I don't understand your last requirement (modified column names): How could this happen when you query just a single table?

    Since I don't know enough about SyBase 15 I'd like to leave it for the folks knowing both systems.

    As a side note: it would help others if you could name the columns identifying a single row. I just guessed part_id, adt_ts and adt_actn_cd...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Ganapathi M

    Say Hey Kid

    Points: 702

    I have modified the #tab1_audit and included one more column name as 'Row_number'.

    (i) When a update trigger inserts row into this #tab1_audit, both old and new (Modified) rows will insert with a unique number (say 100 for CO(old) and CN(New)) next to 'adt_actn_cd' column.

    (ii) next modified row will insert with (101 for both CO(old) and CN(New))

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO',100)

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN',100)

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',101)

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN',101)

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',102)

    Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN',102)

    So, we need to check the changes in column values between two rows using Row_number.

    In my live table I have 25 columns. So I need to display changes for all these columns as below

    ------------------------------------------------------------------------

    Part_ID Columns_Chngd OLD_VALUE NEW_VALUE

    -------------------------------------------------------------------------

    1002 pay_clct_am -10800 -11880

    1002 pay_clct_am -11880 -10

    1002 ccy_cd USD INR

    1003 prdct_id VEDF201112 VEDF201111

    1003 tick_dnmtn_am 10000 20000

    -------------------------------------------------------------------------

  • TT_Turtle

    SSC Rookie

    Points: 28

    Ganapathi M - Sunday, April 10, 2011 8:35 PM

    I have modified the #tab1_audit and included one more column name as 'Row_number'. (i) When a update trigger inserts row into this #tab1_audit, both old and new (Modified) rows will insert with a unique number (say 100 for CO(old) and CN(New)) next to 'adt_actn_cd' column.(ii) next modified row will insert with (101 for both CO(old) and CN(New))Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10800,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CO',100)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112', -11880,'P','USD',0.25,10000,'4/8/11 10:29 AM','APADMIN','CN',100)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-11880,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',101)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1002,'VEDF201112',-10,'P','INR',0.25,10000,'4/8/11 11:04 PM','APADMIN','CN',101)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201112',-110,'P','USD',0.25,10000,'4/8/11 11:04 PM','APADMIN','CO',102)Insert into #tab1_audit values (1,'4/6/11 12:00 AM',1003,'VEDF201111',-110,'P','USD',0.25,20000,'4/8/11 11:04 PM','APADMIN','CN',102)So, we need to check the changes in column values between two rows using Row_number. In my live table I have 25 columns. So I need to display changes for all these columns as below------------------------------------------------------------------------Part_ID Columns_Chngd OLD_VALUE NEW_VALUE-------------------------------------------------------------------------1002 pay_clct_am -10800 -118801002 pay_clct_am -11880 -101002 ccy_cd USD INR1003 prdct_id VEDF201112 VEDF2011111003 tick_dnmtn_am 10000 20000-------------------------------------------------------------------------

    Hi, do you mind to share how to do you do for this part? >> So, we need to check the changes in column values between two rows using Row_number. <<

  • pietlinden

    SSC Guru

    Points: 62347

    This post is 8 years old. Why not start your own post?

    And how do you propose to find out what changed by using ROW_NUMBER()? Even if you did use a windowing function, I don't see how you would identify what changed. You could do it with a trigger if you're changing a value in the table. Are you just adding a new record with one or more columns having new values?

Viewing 7 posts - 1 through 7 (of 7 total)

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