CODEWITHSUNDEEP
sqlt-sql
Nathan Holmes
Nathan Holmes

Reputation: 105

How to compare rows for differences in SQL

I have a table with DocNum, DocVer, ClientNum, MatterNum. Some docs have versions with different ClientNum/MatterNum combinations and I need to create a report with that information.

1 - I've created a table with DocNum, DocVer, ClientNum, MatterNum where the version is > 1 since only docs with more than a single version are possibly affected.

2 - I'm trying to figure out how best to compare all versions 2+ to the version 1 for a specific doc and denote what doesn't match. (Group By DocNum, ClientNum, MatterNum, or Join, Etc.)

I would like a NULL to denote any version 2+ that don't match the ClientNum and MatterNum of the first version.

Sample Table Data:

docnum, version, client, matter    
351,    1,       15000,  00010    
351,    2,       15000,  00020

The desired output would be a column that denotes docnums and versions that do not match the version 1.

docnum, version, client, matter, matched 

351,    1,       15000,  00010, y  

351,    2,       15000,  00020, n

Upvotes: 0

Views: 52

Answers (2)

jimmy8ball
jimmy8ball

Reputation: 756

You could use a self join based on the version number and test the matter column for a change in value, You'll get the idea:

 declare @test table (docnum int, version int, client nvarchar(10), matter nvarchar(10));
 insert into @test
 values
 (351, 1, '15000', '00010'),
 (351, 2, '15000', '00020')

 -- assumes your version increases sequentially
 select t1.docnum, t1.matter, t2.matter, case when t1.matter <> t2.matter then 'flag' else 'ok' end [flag]
 from @test t1
 left join @test t2
 on t1.client = t2.client
 and t1.docnum = t2.docnum
 and t1.version = t2.version - 1

Upvotes: 1

SQLChao
SQLChao

Reputation: 7837

You can join your new table that has only version 2+ records back to the original table's version 1 record. Using case you can check if they match and display null if they don't.

SELECT 
    yt.DocNum
  , yt.DocVer
  , CASE WHEN yt.ClientNum <> ot.ClientNum THEN NULL ELSE yt.ClientNum END AS 'ClientNum'
  , CASE WHEN yt.NatterNum <> ot.MatterNum THEN NULL ELSE yt.MatterNum END AS 'MatterNum'
FROM YourTable yt -- table with versions 2+
JOIN OriginalTable ot 
  ON yt.DocNum = ot.DocuNum AND ot.DocVer = 1

Upvotes: 1

Related Questions