Data comparison within a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data comparison within a table

Author

Topic

dmcinally
Starting Member

2 Posts

Posted - 2004-10-05 : 18:55:24

Hello, This is a tough one for me anyway. I have a set of tables, and in the tables I have versioned data - e.g. I create a complete copy of the set of data and increment the version number. I then make modifications to some of the fields. I would like to be able to compare one version to another version. In the case where there is no data on the earlier version - I would like to display 'N/A', in the case where there is data in the earlier version and not the more recent version - I would like to display 'DELETED', in the case where there is data in both - I would like to display the column name, previous value and current value.

Thanks for the help
DAvid

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-06 : 00:33:00

may be you should use triggers on insert and update on your tables?

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-06 : 03:05:14

Now, when you say, you have created a copy of the data, have you made the copy in to the same table?
How would you determine which version is laterst (any date column to compare againest?)
How would you compare previous and current version ? (what is the matching key)

Post the table structure with some sample data, so I can understand the requirement better.

Hemanth Gorijala
BI Architect / DBA...

dmcinally
Starting Member

2 Posts

Posted - 2004-10-06 : 13:26:17

The data is copied into the same table with a new version number - the key consists of an id, user_id and a version number. It is the version number that gets incremented with every copy. the rest of the information in the table is just mixed data - string, numeric, etc.

what I need from the comparison is the column(s) that changed between any two user selected versions - e.g. version 1 and 5 or version 3 and 4, etc. I also need the data that changed between the two selected versions - perhaps an output similar to

Column Changed Version A data Version B data
-------------- ----------------- -----------------
description this was the first version this is changed

Thanks
David

Bitz
Starting Member

19 Posts

Posted - 2004-10-06 : 14:35:03

David,

The query would be something like this:

SELECT Field1 = CASE
WHEN a.Field1 IS NULL AND b.Field1 IS NOT NULL THEN 'ADDED'
WHEN a.Field1 IS NULL AND b.Field1 IS NULL THEN 'N/A'
WHEN a.Field1 IS NOT NULL AND b.Field1 IS NULL THEN 'DELETED'
WHEN a.Field1 IS NOT NULL AND b.Field1 IS NOT NULL AND a.Field1 <> b.Field1 THEN 'UPDATED'
WHEN a.Field1 IS NOT NULL AND b.Field1 IS NOT NULL AND a.Field1 = b.Field1 THEN 'NO CHANGE'
END

FROM Table a -- Current Version
INNER JOIN Table b --Previous Version
ON a.Id = b.Id
AND a.UserId = b.UserId
AND a.Version = b.Version - 1
WHERE a.Version = blah

You will have to do some fiddling to get the Id, UserId, and Version parts of the key to match (most likely using SUBSTRING or LEFT/RIGHT functions) since they are in the same field.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-06 : 18:04:44

[code]Select orig.ID,orig.version, orig.columnName, chng.version, chng.columnName
from tblWhatever orig inner join

(Select ID,version, columnName
From tblWhatever) chng on

chng.ID = orig.ID and
chng.version > orig.version and
chng.columnName<>orig.columnName[/code]

comparing just previous version (asuming the version is allways incremented by one)
[code]Select orig.ID,orig.version, orig.columnName, chng.version, chng.columnName
from tblWhatever orig inner join

(Select ID,version, columnName
From tblWhatever) chng on

chng.ID = orig.ID and
chng.version = orig.version + 1 and
chng.columnName<>orig.columnName[/code]

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-07 : 02:31:10

quote:
Originally posted by dmcinally

what I need from the comparison is the column(s) that changed between any two user selected versions - e.g. version 1 and 5 or version 3 and 4, etc.

Similar to Bitz, with small changes..

SELECT CHANGE_ACTION = CASE
WHEN b.Field1 IS NULL THEN 'N/A'
WHEN b.Field1 IS NOT NULL and a.Field1 IS NULL THEN 'DELETED'
WHEN a.Field1 IS NOT NULL AND b.Field1 IS NOT NULL AND a.Field1 <> b.Field1 THEN 'UPDATED'
WHEN a.Field1 IS NOT NULL AND b.Field1 IS NOT NULL AND a.Field1 = b.Field1 THEN 'NO CHANGE'
END
, a.field1 as VERSION_A_DATA
, b.field1 as VERSION_B_DATA

FROM Table a -- Current Version
LEFT OUTER JOIN JOIN Table b --Previous Version
ON a.Id = b.Id
AND a.UserId = b.UserId
and a.Version = @Current_Version and b.version = @Previous_Version -- since you might want to compare version 1 and 5

Hemanth Gorijala
BI Architect / DBA...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources