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
Can any one help me with above requirement please?
Regards,
R
-
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