A diagnosis feed can carry codes that are not ICD-10-CM at all. Check every code against a reference list with an anti-join:
SELECT d.encounter_id, d.icd10_code
FROM hd_diagnoses_raw d
LEFT JOIN hd_icd10_ref r ON r.icd10_code = d.icd10_code
WHERE r.icd10_code IS NULL;
Then sort what you find:
- Formatting slips: a lower-case letter, a trailing space.
upper(trim(code)) makes them valid, so the load can fix them.
- Wrong codes: a category without its subcode (
I50), extra characters, a subcode that does not exist. No cleanup can know what the coder meant; they go back to coding.
- Rows loaded twice repeat every column. A patient's second stay is not a duplicate row.
- Values typed differently:
inpatient, Inpatient, IP. Map them to one value before you count.
Codes still invalid after cleaning: these go back to coding.