When a patient is registered twice, each registration gets its own MRN. The person now has two charts: patient counts are one too high, results and allergies are split between records, and a readmission can look like a first visit. The MRN cannot find these duplicates. Match on what describes the person:
SELECT first_name, lower(trim(last_name)) AS last_name_key,
birth_date, COUNT(*) AS registrations
FROM hd_registrations_raw
GROUP BY first_name, lower(trim(last_name)), birth_date
HAVING COUNT(*) > 1;
- Normalize before you compare.
lower(trim(...)) makes OKAFOR, Okafor and Okafor one value. Grouping on the raw text misses them.
- A name alone is not enough. Different people share names; the birth date separates them.
- A match is a candidate, not a merge. Health information management staff confirm a pair before the records are merged.
People registered more than once, whatever the capitals and spaces.