CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sri Reddy
Sri Reddy

Reputation: 7012

Find duplicate records in two group of data in one sql server table

I have one sql server (2008) table containing group of data.

Source    PersonId    Date      Description   Code     IsDup
------    --------    -----     -----------   ------   ----- 
EHR       1234        1/1/2012  Fever         120.12
EHR       1234        6/1/2012  Fracture      101.00
EHR       1234        11/4/2012 Hypertension  223.15
RAM       1234        1/1/2012  Fever         120.12              <-- Duplicate
RAM       1234        6/1/2012  Fracture      101.00              <-- Duplicate
RAM       1234        4/1/2012  Diabetic      601.00
TAR       1234        2/1/2012  Asthma        456.00
TAR       1234        1/1/2012  Fever         120.12              <-- Duplicate

I need to compare the data between the different groups. "EHR" being the master group, I need to check if any other group has data exactly matching that in "EHR" master group within the table. And then it should update the IsDup column with 1.

Expected Result:

Source    PersonId    Date      Description   Code     IsDup
------    --------    -----     -----------   ------   ----- 
EHR       1234        1/1/2012  Fever         120.12
EHR       1234        6/1/2012  Fracture      101.00
EHR       1234        11/4/2012 Hypertension  223.15
RAM       1234        1/1/2012  Fever         120.12     1
RAM       1234        6/1/2012  Fracture      101.00     1
RAM       1234        4/1/2012  Diabetic      601.00
TAR       1234        2/1/2012  Asthma        456.00
TAR       1234        1/1/2012  Fever         120.12     1

I know how to check for duplicates within the table but not sure how can we do comparison keeping one group static.

I got this from one of the stackoverflow thread to identify dups but how to add grouped comparision:

with x as   (select  *, rn = row_number()
            over(PARTITION BY [PersonId], [Date], [Description], [Code] order by [PersonId], [Date], [Description], [Code])
            from    Results)

select * from x
where rn > 1

Upvotes: 0

Views: 1423

Answers (4)

Nishad
Nishad

Reputation: 426

Try this please..

   update tab
   set tab1.isDup=1
   from table1 tab1, table1 tab2
   where 
   tab1.PersonId=tab1.PersonId and
   tab1.Date=tab2.Date and
   tab1.desription=tab2.desription and
   tab1.Code=tab2.Code and
   tab1.Source != tab2.source

Upvotes: 0

Lamak
Lamak

Reputation: 70648

This should do:

UPDATE A
SET IsDup = 1 
FROM YourTable A
WHERE [Source] != 'EHR'
AND EXISTS (SELECT 1 FROM YourTable
            WHERE [Source] = 'EHR'
            AND PersonId = A.PersonId 
            AND [Date] = A.[Date]
            AND Description = A.Description
            AND Code = A.Code)

Here is a demo for you to try.

Upvotes: 1

codingbadger
codingbadger

Reputation: 43984

Try this:

;With rootQuery as 
(
Select  SOURCE, PersonId, Date, Description, Code
From    MedicalHistory 
Where   Source = 'EHR'
)
Update mhd
Set IsDuplicate = 1
From    rootquery mh 
Join    MedicalHistory mhd on mh.PersonId = mhd.PersonId
Where   mh.Description = mhd.Description
And     mh.Code = mhd.Code
And         mh.Date = mhd.Date
And     mhd.Source != 'EHR'

Upvotes: 0

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You can update your table using self join :

update r1 set isDup = 1
from results r1 join results r2 on 
     r1.PersonId = r2.PersonId and r1.Date = r2.Date and 
     r1.Description = r2.Description and r1.Code = r2.Code 
where r1.Source <> 'EHR' and r2.Source = 'EHR'

Upvotes: 2

Related Questions