I'm trying to write a query to identify duplicate patient records.
Basically I want to pull patients from the same table that have the same fname, lname, and DOB but have different unique ids.
My first try was:
select d2.patnum as id,d2.patnum,d2.lname,d2.fname,d2.birthdate from patient as d1, patient as d2 where d1.lname=d2.lname and d1.fname=d2.fname and d1.birthdate=d2.birthdate and d1.patnum!=d2.patnum order by d2.lname, d2.fname
Which didn't work because it includes deleted patients so I added "and d2.patstatus != 4" at the end (before order by) which got rid of deleted patients but not the matching record of the deleted patient.
I figured what I needed to do was create a temp table of "non-deleted" patients to draw from so I wrote:
select d2.patnum as id,d2.patnum,d2.lname,d2.fname,d2.birthdate from (select * from patient where patient.patstatus !=4) as d1, (select * from patient where patient.patstatus !=4) as d2 where d1.lname=d2.lname and d1.fname=d2.fname and d1.birthdate=d2.birthdate and d1.patnum!=d2.patnum order by d2.lname, d2.fname
Which caused Opendent to close down all together.
Can someone help with this problem
Duplicate Records Query
-
- Posts: 3
- Joined: Thu Jul 26, 2007 8:46 am
- Location: St. David's Community Health Foundation
- jordansparks
- Site Admin
- Posts: 5739
- Joined: Sun Jun 17, 2007 3:59 pm
- Location: Salem, Oregon
- Contact:
Are you using Oracle or MySQL?
Try:
SELECT COUNT(*) AS repetitions, LName, FName, Birthdate
FROM patient
WHERE PatStatus!=4
GROUP BY LName,FName,Birthdate
HAVING repetitions > 1
The reason I ask about Oracle is that, when you use a GROUP BY clause, it might require you to include all 4 columns (??). So I don't know how it handles the repetitions peudocolumn. But I tested it on my computer with MySQL and it works.
Try:
SELECT COUNT(*) AS repetitions, LName, FName, Birthdate
FROM patient
WHERE PatStatus!=4
GROUP BY LName,FName,Birthdate
HAVING repetitions > 1
The reason I ask about Oracle is that, when you use a GROUP BY clause, it might require you to include all 4 columns (??). So I don't know how it handles the repetitions peudocolumn. But I tested it on my computer with MySQL and it works.
Jordan Sparks, DMD
http://www.opendental.com
http://www.opendental.com
-
- Posts: 3
- Joined: Thu Jul 26, 2007 8:46 am
- Location: St. David's Community Health Foundation