EPISODE_OF_CARE Data Quality Issue: Inflation in Active Patient Count

[EddieDavison92]

Summary

I'm seeing a discrepancy in active patient counts for Brondesbury Medical Centre between systems, with UAT data showing ~50% more active patients than expected when using EPISODE_OF_CARE, but correct counts when using PATIENT_REGISTERED_PRACTITIONER_IN_ROLE to determine registration status.

This issue isn't a priority since we have a workaround, but looks to be a genuine problem.

Issue Details

Expected vs Actual Patient Counts:

• HealtheIntent: 23,062 active patients
• UAT Data (via EPISODE_OF_CARE): 34,445 patients with active episodes ❌
• UAT Data (via PATIENT_REGISTERED_PRACTITIONER_IN_ROLE): 23,821 patients ✅

The 23,821 figure aligns well with HealtheIntent when accounting for opt-out rate.

Root Cause Analysis

The issue appears to be with the EPISODE_OF_CARE data:

• Only 165 patients have closed episodes of care with end dates
• This means 99.5% of episodes remain "active" indefinitely

This suggests episode of care data is including historical patients and not properly closing the episodes

Key Questions

• Should the Episode of Care end when the patient is deducted (left the practice)?
• Should the Episode of Care end when the patient is deceased?

SQL Queries

Correct Patient Count:

-- This query returns the CORRECT count: 23,821
SELECT 
    COUNT(DISTINCT pr."patient_id") as registered_patient_count
FROM 
    "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT_REGISTERED_PRACTITIONER_IN_ROLE" pr
    INNER JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."ORGANISATION" o
        ON pr."organisation_id" = o."id"
WHERE 
    pr."start_date" IS NOT NULL
    AND pr."end_date" IS NULL  -- Currently active registrations

This version has the exact same 23,821 count

-- This query returns the CORRECT count: 23,821
SELECT 
    COUNT(DISTINCT pr."patient_id") as registered_patient_count
FROM 
    "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT_REGISTERED_PRACTITIONER_IN_ROLE" pr
    INNER JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."ORGANISATION" o
        ON pr."organisation_id" = o."id"
WHERE 
    pr."start_date" IS NOT NULL
    AND (
        pr."end_date" IS NULL  -- No end date
        OR pr."end_date" > CURRENT_DATE()
        OR pr."end_date" < pr."start_date" 
    );

Shows 99.5% of patients have active episodes

-- 
WITH patient_episode_status AS (
    SELECT 
        p."id" as patient_id,
        MAX(CASE WHEN e."episode_of_care_start_date" IS NOT NULL 
                 AND e."episode_of_care_end_date" IS NULL 
            THEN 1 ELSE 0 END) as has_active_episode
    FROM 
        "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT" p
        LEFT JOIN "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE" e
            ON p."id" = e."patient_id"
    GROUP BY 
        p."id"
)
SELECT 
    COUNT(*) as total_patients,
    COUNT(CASE WHEN has_active_episode = 1 THEN 1 END) as patients_with_active_episodes,
    COUNT(CASE WHEN has_active_episode = 0 THEN 1 END) as patients_without_active_episodes
FROM 
    patient_episode_status;
-- Returns: 34,610 total | 34,445 with active | 165 without active

Shows 165 patients with closed episodes of care

SELECT COUNT(DISTINCT p."id") as patients_with_only_closed_episodes
FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."PATIENT" p
WHERE p."id" IN (
    -- Patients who have episodes
    SELECT DISTINCT "patient_id" 
    FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE"
    WHERE "patient_id" IS NOT NULL
)
AND p."id" NOT IN (
    -- Patients who have at least one active episode
    SELECT DISTINCT "patient_id" 
    FROM "Data_Store_OLIDS_UAT"."OLIDS_MASKED"."EPISODE_OF_CARE"
    WHERE "patient_id" IS NOT NULL
    AND "episode_of_care_start_date" IS NOT NULL
    AND "episode_of_care_end_date" IS NULL
);