0

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.

8
  • Start by fixing your schema. There should not be different tables for the same entity. Have one table for the measurements and a column in it that holds a timestamp when the measurement has been taken.
    – sticky bit
    Commented Oct 28, 2021 at 10:05
  • thanks for that, this query would not normally be needed as the system I wrote actually tracks changes in the way you mention. however, this situation occurred due to a problem with my customer where they didn't use my system and I'm now trying to update them with changes. this is a temp fix Commented Oct 28, 2021 at 10:18
  • Imo - you should always keep the raw data as-is. It's only a matter of time before someone asks "what was the value on this date?" Commented Oct 28, 2021 at 10:26
  • @MichaelKutz 100% agree, and I do have a separate table called SampleHistory that tracks all of the data changes with the date and time stamps, however, the customer decide to let some untrained person add data directly into my database tables, not via my VB front end, so now I need to rebuild the history from two backup files where I know data is correct. any ideas? Commented Oct 28, 2021 at 10:32
  • 1
    Start by posting DDL and, if needed (and most likely is), explaining how you use the table and what the columns represent in sufficient detail to understand how you associate one row in the first table with another row in the second table. It is important to know the primary and unique keys.
    – SMor
    Commented Oct 28, 2021 at 11:37

3 Answers 3

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

0

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

enter image description here

1
  • Thanks for this, it worked, when I combined it with HandyD's much appricated. Commented Oct 29, 2021 at 12:20
0

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.

1
  • Thanks for this, it worked much appreciated.this was only needed as a temp fix as I wrote a VB front end that tracked changes already, but this client allowed a non trained user to put data into the database manually. Which screwed it all up. Thanks to you it's sorted now. Commented Oct 29, 2021 at 12:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.