EPISODE_OF_CARE: Patients missing Episode of Care records

[EddieDavison92]

Problem Summary

• 3924 patients (11%) are missing an episode of care record.
• It is not possible to determine the patients registration start and end date for patients missing an Episode of Care record.
• Additionally, a small number of patients are completely orphaned (no patient.registered_practice_id or record in Episode of Care).

Impact

• Therefore attributing persons (which span multiple patient records) to a single currently registered practice won't work reliably.

Evidence

Metric	Value	%
Total Patients	34,610	100%
Patients Missing Episode of Care Records	3,924	11.34%
Dummy Patients	0	-
Has Current Practice Registration (patient.registered_practice_id)	3,889 out of 3,924	99.1%
Has NHS Number Hash	3,903 out of 3,924	99.5%
Completely Orphaned	35	0.9%

Query:

You can use this query targeting the UAT data to generate the above table.

WITH patients_without_episodes AS (
    SELECT 
        p."id" as patient_id,
        p."is_dummy_patient",
        p."registered_practice_id",
        p."record_owner_organisation_code",
        p."birth_year",
        p."nhs_number_hash"
    FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT" p
    LEFT JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE" eoc 
        ON p."id" = eoc."patient_id"
    WHERE eoc."patient_id" IS NULL
),
total_patients AS (
    SELECT COUNT(*) as total_count
    FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT"
    WHERE NOT "is_dummy_patient"
)

SELECT 
    'Impact Summary' as section_type,
    'Total Patients' as metric,
    tp.total_count as value,
    100.0 as percentage,
    'All real patients in system' as description
FROM total_patients tp

UNION ALL

SELECT 
    'Impact Summary',
    'Patients Missing Episode of Care Records',
    COUNT(*),
    ROUND(COUNT(*)::FLOAT / (SELECT total_count FROM total_patients) * 100, 2),
    'Real patients with no registration history'
FROM patients_without_episodes
WHERE NOT "is_dummy_patient"

UNION ALL

SELECT 
    'Impact Summary',
    'Have Current Practice Registration (registered_practice_id)',
    COUNT(CASE WHEN "registered_practice_id" IS NOT NULL THEN 1 END),
    ROUND(COUNT(CASE WHEN "registered_practice_id" IS NOT NULL THEN 1 END)::FLOAT / NULLIF(COUNT(*), 0) * 100, 1),
    'Of affected patients with current GP practice assignment'
FROM patients_without_episodes
WHERE NOT "is_dummy_patient"

UNION ALL

SELECT 
    'Impact Summary',
    'Have NHS Number Hash',
    COUNT(CASE WHEN "nhs_number_hash" IS NOT NULL THEN 1 END),
    ROUND(COUNT(CASE WHEN "nhs_number_hash" IS NOT NULL THEN 1 END)::FLOAT / NULLIF(COUNT(*), 0) * 100, 1),
    'Of affected patients with valid NHS numbers'
FROM patients_without_episodes
WHERE NOT "is_dummy_patient"

UNION ALL

SELECT 
    'Impact Summary',
    'Completely Orphaned (no registered_practice_id)',
    COUNT(CASE WHEN "registered_practice_id" IS NULL THEN 1 END),
    ROUND(COUNT(CASE WHEN "registered_practice_id" IS NULL THEN 1 END)::FLOAT / NULLIF(COUNT(*), 0) * 100, 1),
    'No current GP practice AND no episode records'
FROM patients_without_episodes
WHERE NOT "is_dummy_patient"

UNION ALL

SELECT 
    'Validation Checks',
    'Patient Type Confirmation',
    COUNT(CASE WHEN "is_dummy_patient" = FALSE THEN 1 END),
    0.0,
    'Confirmed real patients (is_dummy_patient = FALSE)'
FROM patients_without_episodes

UNION ALL

SELECT 
    'Validation Checks',
    'Dummy Patients in Affected Set',
    COUNT(CASE WHEN "is_dummy_patient" = TRUE THEN 1 END),
    0.0,
    'Should be 0 - confirms this affects real data'
FROM patients_without_episodes

UNION ALL

SELECT 
    'Validation Checks',
    'NULL Dummy Flags',
    COUNT(CASE WHEN "is_dummy_patient" IS NULL THEN 1 END),
    0.0,
    'Patients with undefined dummy status'
FROM patients_without_episodes

UNION ALL

SELECT 
    'Data Quality Indicators',
    'Episode of Care Coverage',
    (SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT" p 
     JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE" eoc ON p."id" = eoc."patient_id"
     WHERE NOT p."is_dummy_patient"),
    ROUND((SELECT COUNT(*) FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT" p 
           JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE" eoc ON p."id" = eoc."patient_id"
           WHERE NOT p."is_dummy_patient")::FLOAT / 
          (SELECT total_count FROM total_patients) * 100, 2),
    'Real patients WITH episode of care records'

UNION ALL

SELECT 
    'Data Quality Indicators',
    'Missing Registration History',
    COUNT(*),
    ROUND(COUNT(*)::FLOAT / (SELECT total_count FROM total_patients) * 100, 2),
    'Real patients WITHOUT any registration history'
FROM patients_without_episodes
WHERE NOT "is_dummy_patient"

ORDER BY 
    CASE section_type
        WHEN 'Impact Summary' THEN 1
        WHEN 'Validation Checks' THEN 2
        WHEN 'Data Quality Indicators' THEN 3
    END,
    metric;

[Rkw20067]

Rich W to investigate as part of triage.

[EddieDavison92]

Re-ran test:

Number missing episode of care records dropped from 3924 to 1237.

Only 3.5% of patients do not have an episode of care record now, a significant improvement from the 11% starting position.

Number of completely orphaned records reduced from 35 to 31.

[Rkw20067]

Retested in TEST enviroment. Following change means that all patients will have an associated episode of care.
https://dev.azure.com/NELAnalytics/LondonDataService/_git/SQL/pullrequest/2267