I want to find out what data has changed across a row from table 1 to tabl 2 [duplicate]

Question

I have two identically set up tables that track water samples, the first table was in June and the second was taken in November. I need to produce a report on what values have changed over time.

I have a unique column called "SampleNumber" and the tables look as below, also see the result I want, how is this possible?

June Sample Table: Called 'OLDSample'

SampleNumber	BatchNumber	Mass	User01
S00000001	NH2318	15	BLUE
S00000002	HY1318	5	GREEN
S00000003	HH2098	10	YELLOW

November Sample Table: Called 'Sample'

SampleNumber	BatchNumber	Mass	User01
S00000001	NH2318	15	BLUE
S00000002	HY1320	5	GREEN
S00000003	HH3098	10	PINK

Report data needed in a new table called 'History'

SampleNumber	Notes
S00000002	BatchNumber was HY1318 now HY1320
S00000003	BatchNumber was HH2098 now HH3098 User01 was YELLOW now PINK

So basically the query must ignore anything that is the same in both databases but highlight those that have differences, with a copy of the old and the new values for each column.

any help or pointing in the right direction would be fantastic. I've taught myself SQL and can handle basic queries but this is a bit beyond me.

Answer 1

You can join these tables by the sample number, and filter the results to identify what are the samples with differences. Then you cant use the return of this query on a report, or create other table to store it.

--Assuming by the description
create table OLDSample(SampleNumber varchar(200), BatchNumber varchar(200), Mass int, User01 varchar(200))
insert into OLDSample values
('S00000001', 'NH2318', 15, 'BLUE'),
('S00000002', 'HY1318', 5, 'GREEN'),
('S00000003', 'HH2098', 10, 'YELLOW')

create table [Sample](SampleNumber varchar(200), BatchNumber varchar(200), Mass int, User01 varchar(200))
insert into [Sample] values
('S00000001', 'NH2318', 15, 'BLUE'),
('S00000002', 'HY1320', 5, 'GREEN'),
('S00000003', 'HH3098', 10, 'PINK')

select
    s.SampleNumber,
    s.BatchNumber, old.BatchNumber as BatchNumber_Old,
    s.Mass, old.Mass as Mass_Old,
    s.User01, old.User01 as User01_Old
from [Sample] as [s]
left join [OLDSample] [old] on s.SampleNumber=old.SampleNumber
where s.BatchNumber<>old.BatchNumber or s.Mass<>old.Mass or s.User01<>old.User01
or old.SampleNumber is null -- In case you want the new samples, that don't have old values.

Example on dbfiddle

Answer 2

Using your sample data, it looks like this is what you want.

SELECT S.SampleNumber AS RecentSampleNumber, S.BatchNumber AS RecentBatchNumber,
S.Mass AS RecentMass,S.User01 AS RecentUser ,OS.SampleNumber AS OldSampleNumber, OS.BatchNumber AS OldBatchNumber,
OS.Mass AS OldMass, OS.User01 AS OldUser

FROM dbo.Sample AS S
INNER JOIN dbo.OLDSample AS OS ON S.SampleNumber = OS.SampleNumber
WHERE S.BatchNumber <> OS.BatchNumber 
    OR S.Mass <> OS.Mass
    OR S.User01 <> OS.User01

Answer 3

Borrowing the sample data from Jorge Bugal's answer, below is a query to return all of the new sample values as well as the old sample values if they exist. If you only want to see values where there is existing older values change the JOIN to an INNER.

Query (db<>fiddle):

SELECT n.SampleNumber
  , NewBatchNumber
  , NewMass
  , NewUser01
  , OldBatchNumber
  , OldMass
  , OldUser01
FROM
(
  SELECT SampleNumber
    , BatchNumber AS NewBatchNumber
    , Mass AS NewMass
    , User01 AS NewUser01
  FROM Sample
  EXCEPT 
  SELECT SampleNumber
    , BatchNumber
    , Mass
    , User01
  FROM OLDSample
) n
LEFT JOIN (
  SELECT SampleNumber
    , BatchNumber AS OldBatchNumber
    , Mass AS OldMass
    , User01 AS OldUser01
  FROM OLDSample
) o ON o.SampleNumber = n.SampleNumber

This solution is useful if you have larger numbers of columns to compare as you don't need to write out individual inequality clauses for each column in a WHERE clause. Instead, it uses EXCEPT to eliminate rows in the first table that have an exact match in the second table. Any column difference means no match.

This is a viable solution for your immediate reporting problem. Long term, consider looking into Temporal Tables or Change Tracking to keep a history of changes to your data.