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

Question

Post reply

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

Ravi

Old Hand

Points: 333
More actions
November 25, 2022 at 11:03 am

#4117925
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
Can any one help me with above requirement please?

Regards,
R
- This topic was modified 1 year, 12 months ago by Ravi.
- This topic was modified 1 year, 12 months ago by Ravi.
- This topic was modified 1 year, 12 months ago by Ravi.
Attachments:
You must be logged in to view attached files.

November 29, 2022 at 8:10 am

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

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

Ravi Old Hand Points: 333 More actions · Answer 3

Thanks Johan.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

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

Ravi Old Hand Points: 333 More actions · Answer 8

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

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!

😎

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

Attachments: