CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 2472

SQL count of data in many to one relationship where I want to find duplicate data

I have a 3 table structure such that:

Table Patient - PatientID PK has a 1 to many relationship with table Appointment (PK ApptID) Table Appointment has a 1 to many relationship with table Encounter (PK EncounterID). ApptID is a FK for Encounter table.

Encounter table has a few notable fields. PK: EncounterID, FK: ApptID, DateofEncounter (datetime), and Code (varchar). The last two are not keys or anything.

What I want to do search for duplicate encounters that have the same code, same ApptID, and same DateoFEncounter. I want a list by PatientID, then ApptID

I have this SQL which lists PatientID, ApptID, and # of Encounters. But I don't know if those encounters are duplicates or not. (they could be on different dates, or different codes, or what not). I have no clue how to find those that occur on the same date with the same code. Any advice?

select a.PatientID, a.ApptID, COUNT(e.EncounterID) AS NumberOfEncounters
FROM Appointment a LEFT JOIN Encounter e on e.ApptID = a.ApptID
GROUP BY a.PatientID, a.ApptID
ORDER BY COUNT(e.EncounterID) DESC

Upvotes: 0

Views: 520

Answers (1)

nw.
nw.

Reputation: 5155

select a.PatientID, a.ApptID, COUNT(e.EncounterID) AS NumberOfEncounters
FROM Appointment a LEFT JOIN Encounter e on e.ApptID = a.ApptID
                            AND EXISTS (SELECT * FROM Encounter e2 WHERE e.pkEncounterID <> e2.pkEncounterID AND e.Code = e2.Code AND e.DateofEncounter = e2.DateofEncounter AND e.ApptID = e2.ApptID)
GROUP BY a.PatientID, a.ApptID
ORDER BY COUNT(e.EncounterID) DESC

Upvotes: 1

Related Questions