Produce report on unknown codes in TPP database

[inglesp]

For each of the following columns in TPP:

• APCS.Der_Diagnosis_All (ICD-10)
• APCS_Der.Spell_Primary_Diagnosis (ICD-10)
• CodedEvent.CTV3Code (CTV3)
• CodedEvent_SNOMED.ConceptID (SNOMED-CT)
• MedicationDictionary.DMD_ID (dm+d) [will need special casing, because of extra multilex lookup]
• ONS_Deaths.icd10u, etc (ICD-10)
• EC_Diagnosis.EC_Diagnosis_XX (SNOMED-CT)
• EC_Diagnosis.Discharge_Destination_SNOMED_CT (SNOMED-CT)

we want to know whether the column contains any codes that are not in the version of the underlying coding system that's currently being used in OpenCodelists.

For each column, we should produce a report with counts of unknown codes, and a graph of the number of unknown codes over time.

This report should be generalisable to other backends in future.

[inglesp]

Blocked while we wait clarity on whether we can use SQL Runner for this.

[milanwiedemann]

at this point, do we want to report/visualise each specific code that is unknown by month (e.g., 2022-01: Code123, Code456) or just the sum of unknown codes, (e.g., 2022-01: 2 unknown codes)?

[inglesp]

Could we do both?

[inglesp]

Analysis here: https://github.com/opensafely/unknown-codes-investigation/issues/4.