Compare 2 tables and highlight where the column values has been updated

  • Hi All,

    I have a requirement to compare data from same table with different snapshot dates and highlight what column values changed from to TO

    compare table

    Can any one help me with above requirement please?

     

    Regards,

    R

    • This topic was modified 2 months, 1 week ago by  Ravi.
    • This topic was modified 2 months, 1 week ago by  Ravi.
    • This topic was modified 2 months, 1 week ago by  Ravi.
    Attachments:
    You must be logged in to view attached files.
  • Quick thought, the results do not match the data posted unless there is some hidden logic 😉

    We will need your help in order to be able to help you, so please help us!

    😎

    It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

  • have you tried Select ... from morerecenttable EXCEPT select ... from oldersnapshot

    This gives you new and modified rows. Having that set, you can compare with individual columns

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks Johan.

    i tried except but how do identify the columns changed with previous snapshot.

  • Assuming SNO is the unique identifier - use an outer join to the old snapshot and filter on any of the columns that have changed.

    SELECT n.sno
    , n.name
    , n.productname
    , n.amount
    , n.snapshotdate
    , old_productname = o.productname
    , old_snapshotdate = o.snapshotdate
    FROM newSnapshot n
    LEFT JOIN oldSnapshot o ON o.sno = d.sno
    WHERE n.name <> o.name
    OR n.productname <> o.productname
    OR n.amount <> o.amount;

    As for highlighting - that would have to be done in the presentation layer.  How you do that will depend entirely on what you are using for the report.  For example, using SSRS I would return new and old columns - and in SSRS setup an expression comparing new vs old - and if different then set the highlight.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • This problem is absolutely elementary but the answer would have been here sooner if the OP had posted the requested DDL and data!

    😎

    Most of us are far too busy to create sample data sets from posted images!!!

    Here is the simplest approach:

    USE TEEST;
    GO
    SET NOCOUNT ON;
    GO

    DECLARE @SNAP01 TABLE
    (
    SNO INT NOT NULL PRIMARY KEY CLUSTERED
    ,[NAME] VARCHAR(5) NOT NULL
    ,PRODUCT VARCHAR(5) NOT NULL
    ,AMOUNT INT NOT NULL
    ,SNAPSHOTDATE DATE NOT NULL
    );
    DECLARE @SNAP02 TABLE
    (
    SNO INT NOT NULL PRIMARY KEY CLUSTERED
    ,[NAME] VARCHAR(5) NOT NULL
    ,PRODUCT VARCHAR(5) NOT NULL
    ,AMOUNT INT NOT NULL
    ,SNAPSHOTDATE DATE NOT NULL
    );
    INSERT INTO @SNAP01 (SNO,[NAME],PRODUCT,AMOUNT,SNAPSHOTDATE)
    VALUES
    (1,'a','ab',100,'2022-11-14')
    ,(2,'b','cd',101,'2022-11-14')
    ,(3,'c','ed',102,'2022-11-14')
    ,(4,'d','rf',103,'2022-11-14')
    ,(5,'e','tb',104,'2022-11-14')
    ,(6,'f','or',105,'2022-11-14')
    ,(7,'g','uf',106,'2022-11-14')
    ;
    INSERT INTO @SNAP02 (SNO,[NAME],PRODUCT,AMOUNT,SNAPSHOTDATE)
    VALUES
    (1,'a','abc',100,'2022-11-21')
    ,(2,'b','cdf',101,'2022-11-21')
    ,(3,'c','ed', 102,'2022-11-21')
    ,(4,'d','rfi',103,'2022-11-21')
    ,(5,'e','tb', 104,'2022-11-21')
    ,(6,'f','or', 105,'2022-11-21')
    ,(7,'g','ufg',106,'2022-11-21')
    ,(8,'hh','li',107,'2022-11-21')
    ,(9,'cc','kl',108,'2022-11-21')
    ;

    SELECT
    S2.SNO
    ,S2.[NAME]
    ,S2.PRODUCT
    ,S2.AMOUNT
    ,S2.SNAPSHOTDATE
    ,CASE
    WHEN S2.PRODUCT <> S1.PRODUCT THEN S1.PRODUCT
    WHEN S2.PRODUCT = S1.PRODUCT THEN ''
    ELSE 'na'
    END AS PREVIOUS_PRODUCT
    FROM @SNAP02 S2
    LEFT OUTER JOIN @SNAP01 S1
    ON S2.SNO = S1.SNO
    ;

    The result set:

    SNO         NAME  PRODUCT AMOUNT      SNAPSHOTDATE PREVIOUS_PRODUCT
    ----------- ----- ------- ----------- ------------ ----------------
    1 a abc 100 2022-11-21 ab
    2 b cdf 101 2022-11-21 cd
    3 c ed 102 2022-11-21
    4 d rfi 103 2022-11-21 rf
    5 e tb 104 2022-11-21
    6 f or 105 2022-11-21
    7 g ufg 106 2022-11-21 uf
    8 hh li 107 2022-11-21 na
    9 cc kl 108 2022-11-21 na

     

  • Just further on this subject for the sake of completion, what if entries are deleted?

    😎

    Here is a simple suggestion:

    USE TEEST;
    GO
    SET NOCOUNT ON;
    GO
    ---------------------------------------------------------------------
    -- https://www.sqlservercentral.com/forums/topic/compare-2-tables-and-highlight-where-the-column-values-has-been-updated
    ---------------------------------------------------------------------
    -- Sample data set
    ---------------------------------------------------------------------

    DECLARE @SNAP01 TABLE
    (
    SNO INT NOT NULL PRIMARY KEY CLUSTERED
    ,[NAME] VARCHAR(5) NOT NULL
    ,PRODUCT VARCHAR(5) NOT NULL
    ,AMOUNT INT NOT NULL
    ,SNAPSHOTDATE DATE NOT NULL
    );
    DECLARE @SNAP02 TABLE
    (
    SNO INT NOT NULL PRIMARY KEY CLUSTERED
    ,[NAME] VARCHAR(5) NOT NULL
    ,PRODUCT VARCHAR(5) NOT NULL
    ,AMOUNT INT NOT NULL
    ,SNAPSHOTDATE DATE NOT NULL
    );
    INSERT INTO @SNAP01 (SNO,[NAME],PRODUCT,AMOUNT,SNAPSHOTDATE)
    VALUES
    (1,'a','ab',100,'2022-11-14')
    ,(2,'b','cd',101,'2022-11-14')
    ,(3,'c','ed',102,'2022-11-14')
    ,(4,'d','rf',103,'2022-11-14')
    ,(5,'e','tb',104,'2022-11-14')
    ,(6,'f','or',105,'2022-11-14')
    ,(7,'g','uf',106,'2022-11-14')
    ;
    INSERT INTO @SNAP02 (SNO,[NAME],PRODUCT,AMOUNT,SNAPSHOTDATE)
    VALUES
    (1,'a','abc',100,'2022-11-21')
    ,(2,'b','cdf',101,'2022-11-21')
    ,(3,'c','ed', 102,'2022-11-21')
    ,(4,'d','rfi',103,'2022-11-21')
    ,(5,'e','tb', 104,'2022-11-21')
    ,(6,'f','or', 105,'2022-11-21')
    ,(7,'g','ufg',106,'2022-11-21')
    ,(8,'hh','li',107,'2022-11-21')
    ,(9,'cc','kl',108,'2022-11-21')
    ;
    ---------------------------------------------------------------------
    -- Note that this does not catch deleted entries!
    ---------------------------------------------------------------------

    SELECT
    S2.SNO
    ,S2.[NAME]
    ,S2.PRODUCT
    ,S2.AMOUNT
    ,S2.SNAPSHOTDATE
    ,CASE
    WHEN S2.PRODUCT <> S1.PRODUCT THEN S1.PRODUCT
    WHEN S2.PRODUCT = S1.PRODUCT THEN ''
    ELSE 'na'
    END AS PREVIOUS_PRODUCT
    FROM @SNAP02 S2
    LEFT OUTER JOIN @SNAP01 S1
    ON S2.SNO = S1.SNO
    ;
    ---------------------------------------------------------------------
    -- Deleting one entry from the later snapshot
    ---------------------------------------------------------------------
    DELETE FROM @SNAP02
    WHERE SNO = 2;
    ---------------------------------------------------------------------
    ;WITH BASE_SNO(SNO) AS
    (
    SELECT
    S2.SNO
    FROM @SNAP02 S2
    UNION
    SELECT
    S1.SNO
    FROM @SNAP01 S1
    )
    ,BEEN_DELETED (ALL_SNO,PREV_SNO,LAST_SNO)
    AS
    (
    SELECT
    BS.SNO
    ,S1.SNO
    ,S2.SNO
    FROM BASE_SNO BS
    LEFT OUTER JOIN @SNAP01 S1
    ON BS.SNO = S1.SNO
    LEFT OUTER JOIN @SNAP02 S2
    ON BS.SNO = S2.SNO
    )
    SELECT
    BD.ALL_SNO
    ,S2.SNO
    ,S2.[NAME]
    ,S2.PRODUCT
    ,S2.AMOUNT
    ,S2.SNAPSHOTDATE
    ,CASE
    WHEN S2.PRODUCT <> S1.PRODUCT THEN S1.PRODUCT
    WHEN S2.PRODUCT = S1.PRODUCT THEN ''
    WHEN S2.SNO IS NULL THEN 'DELETED'
    ELSE 'na'
    END AS PREVIOUS_PRODUCT
    FROM BEEN_DELETED BD
    LEFT OUTER JOIN @SNAP02 S2
    ON BD.ALL_SNO = S2.SNO
    LEFT OUTER JOIN @SNAP01 S1
    ON BD.ALL_SNO = S1.SNO
    ;

    Result for deletion:

    ALL_SNO     SNO         NAME  PRODUCT AMOUNT      SNAPSHOTDATE PREVIOUS_PRODUCT
    ----------- ----------- ----- ------- ----------- ------------ ----------------
    1 1 a abc 100 2022-11-21 ab
    2 NULL NULL NULL NULL NULL DELETED
    3 3 c ed 102 2022-11-21
    4 4 d rfi 103 2022-11-21 rf
    5 5 e tb 104 2022-11-21
    6 6 f or 105 2022-11-21
    7 7 g ufg 106 2022-11-21 uf
    8 8 hh li 107 2022-11-21 na
    9 9 cc kl 108 2022-11-21 na

     

     

  • Much easier to just use a FULL OUTER JOIN:

    SELECT
    SNO = COALESCE(S1.SNO, S2.SNO)
    ,S2.[NAME]
    ,S2.PRODUCT
    ,S2.AMOUNT
    ,S2.SNAPSHOTDATE
    ,CASE
    WHEN S2.PRODUCT <> S1.PRODUCT THEN S1.PRODUCT
    WHEN S2.PRODUCT = S1.PRODUCT THEN ''
    WHEN S2.SNO IS NULL THEN 'DELETED'
    ELSE 'na'
    END AS PREVIOUS_PRODUCT
    FROM @SNAP01 S1
    FULL OUTER JOIN @SNAP02 S2 On S1.SNO = S2.SNO;

    If you just want the differences - the left outer join solution will work, but if you need to compare both sides and show inserted vs deleted and changed then a FULL OUTER JOIN works better.

    Either way - 'highlighting' in SQL Server isn't possible so that need to be done in the presentation layer and how that is accomplished will be determined by that layer.  You could also add another column that performs the same checks but returns a different 'color' based on the matching condition - then use that column in your presentation layer to set formatting for the column or row.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • thanks Everyone i have tried using above left outer join using power bi and this resolved my issue with conditional formatting.

    From next time i will try to post as much as info possible to get quick suggestions/replies.

    Kind Regards,

    R

  • Ravi wrote:

    thanks Everyone i have tried using above left outer join using power bi and this resolved my issue with conditional formatting.

    From next time i will try to post as much as info possible to get quick suggestions/replies.

    Kind Regards,

    R

    Good stuff and thanks for the feedback!

    😎

     

  • This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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