EHRs that factor in the following key features can be used by program managers to monitor large-scale non-communicable disease (NCD) programs and are optimized for use in busy primary care facilities that manage thousands of patients with hypertension and diabetes. These conditions require long-term monitoring, team-based care, and data-driven decision-making — which a good EHR can support.

Many EHR features that help manage NCD programs are just as vital in other areas of primary care. For instance, tracking patients who miss follow-up visits is crucial in a hypertension program to ensure continuity of care, and in an immunisation program to identify children who miss their vaccination appointments.

📄 Download the 1-pager summary

1. Data for quality improvement using WHO indicators / HEARTS360 Dashboard
2. Overdue patient line lists
3. Longitudinal patient records
4. Consistent patient ID
5. Clear patient diagnosis
6. Patients “assigned” to a facility
7. Custom report generation

Hearts360.org

This dashboard is a template that can be used by any team working on a hypertension control project. Our team has learned many lessons while developing the Simple.org project and we want to share our best practices with the world. A similar dashboard has been used in India, Bangladesh, Sri Lanka, Ethiopia, and Nigeria to successfully manage over 4 million patients with hypertension.

Please feel free to copy any of the code or ideas that you see in this hypertension dashboard (see open source license below).

📺 View a live example

Automatically generate lists of patients who miss follow-up visits, including their phone numbers, so health workers can quickly reach out and bring them back into care.

💡 Reach out to patients overdue for immunisation or antenatal care appointments.

📄 Link to PRD

Maintain a single patient record across multiple visits. At each follow-up, record only a minimum dataset—blood pressure, blood glucose, medications and follow-up appointment date. This data is sufficient to manage patients over time and monitor a large program.

💡 Scheduling follow-up appointments by default is useful to monitor follow-up care for antenatal care, tuberculosis, immunisation and other health programs.

📄 Link to PRD

Use a consistent patient ID, National Health ID, to identify and track a patient across facilities. At a minimum, within the same software system, the patient has a single longitudinal health record that follows the patient regardless where they seek care.

💡 Useful for all health programs for managing a person’s health from young to elderly.

📄 Link to PRD

To help with patient management; differentiate between patients diagnosed with hypertension from those screened with an elevated BP reading (e.g. specific ICD-10 codes). This enables program managers to trust the data and effectively monitor hypertension indicators (e.g. BP controlled monitoring for patients diagnosed with hypertension).

💡 Infectious disease programs can distinguish between suspected and confirmed cases.

📄 Link to PRD

Assign each patient to a primary facility responsible for their ongoing care, even if they receive services at other facilities. For example, a patient may register at a district hospital but later visit a community clinic. These visits should be linked to the same patient record to ensure continuity of care.

💡 Useful to monitor a patient’s immunisation, maternal health, infectious disease treatment.

📄 Link to PRD

EHRs that support custom reports are useful to drive further insights from the dashboard. Reports that can be downloaded in a simple format helps program managers to assess trends.

💡 Helps managers spot trends and respond quickly.

📄 Link to PRD

A very good public health hypertension dashboard only requires a small number of longitudinal patient data as building blocks:

• Enrolled patients diagnosed with hypertension with Enrollment date and Enrollment facility
• Patients who had a BP measure in the last 3 months and was the BP measure ≥140/90 or <140/90?
• Patients who had a BP measure in the last 12 months
• Patient status as living or died

Data that shows how much of a region's population has lowered blood pressure is also very helpful. If a region does public health surveys, including the Estimated population with hypertension allows you to easily report coverage metrics.

Adding data on drug stock and functioning BP devices helps round out the dashboard to make it even more useful.

30-60% of patients miss their scheduled visits each month. Including scheduled appointment data helps track patients who did not follow-up and enables patient outreach.

This guide explains the how to recreate the HEARTS360 dashboard using example database tables and working SQL queries which can be adapted to work with your own archetecture.

• Patient Categorization
• Database structure example
  • Patients table
  • BP encounters table
• Example queries (detailed)
  • Cumulative dead patients
    • Definition
    • Scenario Example
    • Code Example
    • Data Example
  • Cumulative registered patients and monthly registrations
    • Definition
    • Scenario Example
    • Code Example
    • Data Example
  • Patients under care & lost to follow-up
    • Definition
    • Scenario Example
    • Code Example
    • Data Example
  • Newly registered / Patients under care registered before the past 3 months ('Controlled BP', 'Uncontrolled BP' & 'No visit' )
    • Definition
    • Scenario Example
    • Code Example
    • Data Example
• Hypertension treatment cascade
• Overdue patients
  • Definition
  • Overdue at the start of the month
  • Overdue patients called
  • Overdue patients that returned to care
  • Code example
  • Data example
  • Overdue indicators
  • Overdue patient assumptions
• Additional Dimensions
• Caching results
• Data Checks
• Adapting queries to your data

Patients in the hypertension program are categorized as one of the following:

• Under care
• 12 month lost to follow up
• Dead

Patients marked ‘under care’ fall into one of the 4 following additional categories:

• Newly registered - this category is not visualized in the dashboard
• BP controlled
• BP uncontrolled
• No visit

Depending on the month, a patient may fall under different categories. For example:

• A patient registered in January 2024, is considered Newly Registered for the first 3 months from registration date and from April 2024 moves into either the BP controlled, BP uncontrolled or No Visit category
• Patients under care that have a visit recorded in the past 3 months will be in the BP Controlled or BP Uncontrolled category (depending on their BP reading)
• A patient that does not have a visit recorded in the past 12 months will move to the 12 month lost to follow-up category (up until the 12 month mark, the patient will be recorded in the No Visit category)

As a result, the notion of a REFERENCE MONTH is important for crafting the queries required to get accurate information for each month to build the hypertension treatment indicators.

All elements related to time, should be computed from the perspective of the REFERENCE MONTH and not the current date. This is critically important for information such as:

• Number of BP encounters in the past 3 months
• Number of BP encounters in the past 12 months
• Most recent BP value in the past 3 months

We consider it a good practice to chart the past 18 months of data with the most recent month being the current month.

This example corresponds to the minimal data set required to reproduce the HEART360 dashboard. It does not correspond to a real life example, as tables are missing important information (name/phone number, table ids …)

Data can be organized differently.

Update: contact_number added with overdue patient section.

Cumulative number of dead patients at the end of each month (including patients that have died in previous months).

This should be implemented as a flag during the data collection process where users are allowed to indicate when a patient is deceased. This flag allows dead patients to be excluded from indicators. All historic data of dead patients is excluded from figures.

A patient was registered Sep-2023, and marked dead on Jan-2024. This patient is included in figures Sep-Jan. From the moment they are marked dead all data will be excluded. This patient will no longer be included in any figures for Sep-Jan.

WITH DEAD_PATIENTS_BY_MONTH AS (
    SELECT
        DATE_TRUNC('month',DEATH_DATE) AS REF_MONTH,
        count(\*) AS NB_DEAD_PATIENTS_THAT_MONTH
    FROM patients
    WHERE PATIENT_STATUS = 'dead'
    GROUP BY DATE_TRUNC('month',DEATH_DATE)
)
SELECT
    REFERENCE.REF_MONTH,
    SUM(REFERENCE_BEFORE.NB_DEAD_PATIENTS_THAT_MONTH) AS CUMULATIVE_NUMBER_OF_DEAD_PATIENTS
FROM DEAD_PATIENTS_BY_MONTH REFERENCE
JOIN DEAD_PATIENTS_BY_MONTH REFERENCE_BEFORE ON REFERENCE.REF_MONTH >= REFERENCE_BEFORE.REF_MONTH
GROUP BY REFERENCE.REF_MONTH
ORDER BY REFERENCE.REF_MONTH DESC

Monthly registrations at the end of each month are the patients registered during the month.

Cumulative registered patients at the end of each month are the total number of patients registered before the end of the month and are not dead.

A patient was registered Sep-2023, this patient is counted as a monthly registration for Sep-2023. For cumulative registrations, the patient is counted for Sep-2023 and for every subsequent month as long as they are not marked as dead.

WITH
KNOWN_MONTHS AS (
    SELECT
        DISTINCT(DATE_TRUNC('month',REGISTRATION_DATE)) AS REF_MONTH
    FROM patients
),
PATIENTS_BY_MONTH AS (
    SELECT
        DATE_TRUNC('month',created_at) AS REF_MONTH,
        count(\*) AS NB_NEW_PATIENTS
    FROM patients
    WHERE STATUS <> 'dead'
    GROUP BY DATE_TRUNC('month',created_at)
)
SELECT
    KNOWN_MONTHS.REF_MONTH,
    sum(REFERENCE_BEFORE.NB_NEW_PATIENTS) AS CUMULATIVE_NUMBER_OF_PATIENTS,
    sum(case when KNOWN_MONTHS.REF_MONTH = REFERENCE_BEFORE.REF_MONTH then NB_NEW_PATIENTS else null end) AS NB_NEW_PATIENTS
FROM KNOWN_MONTHS
JOIN PATIENTS_BY_MONTH REFERENCE_BEFORE ON KNOWN_MONTHS.REF_MONTH >= REFERENCE_BEFORE.REF_MONTH
GROUP BY KNOWN_MONTHS.REF_MONTH
ORDER BY KNOWN_MONTHS.REF_MONTH DESC

KNOWN_MONTHS

This is a list of months known by the system. It can be obtained in a variety of ways, but can be derived from the months explicitly existing in the Database as in the example.

ALIVE_PATIENTS

This is a list of all patients that are alive. We are limiting our subquery to the columns that are relevant for this aggregation. Registration date is rounded by month as we’re doing a monthly analysis.

A patient that is not Dead is either Under care or lost to follow-up for a given reference month:

• Under care is a patient with at least one visit recorded in the past 12 months before the end of the reference month
• Lost to follow-up is a patient with no visit recorded in the past 12 months before the end of the reference month

As these two indicators are mutually exclusive (the total of patients under care and patients lost to follow-up should equal cumulative registrations), it can be calculated in a single query.

A patient was registered in Jan-2023, and visited once in Mar-2023. Today is Feb-2024. This patient is counted as a patient under care as they had a visit in the past 12 months.

Using the same patient, today is Mar-2024. This patient is counted as lost to follow-up because they have no visit in the past 12 months.

WITH
KNOWN_MONTHS AS (
    SELECT
        DISTINCT(DATE_TRUNC('month',REGISTRATION_DATE)) AS REF_MONTH
    FROM patients
),
ALIVE_PATIENTS AS (
    SELECT
        DATE_TRUNC('month',REGISTRATION_DATE) AS REGISTRATION_MONTH,
        patient_id
    FROM patients
    WHERE PATIENT_STATUS <> 'dead'
),
BP_ENCOUNTERS AS (
    SELECT
        patient_id,
        DATE_TRUNC('month',ENCOUNTER_DATE) AS BP_ENCOUNTER_MONTH
    FROM blood_pressures
)
SELECT
    KNOWN_MONTHS.REF_MONTH,
    sum(CASE WHEN BP_ENCOUNTERS.patient_id IS NULL THEN 1 ELSE NULL END ) AS NB_PATIENTS_LOST_TO_FOLLOW_UP,
    count(DISTINCT(BP_ENCOUNTERS.patient_id)) AS NB_PATIENTS_UNDER_CARE,
    count(DISTINCT(ALIVE_PATIENTS.patient_id)) AS CUMULATIVE_NUMBER_OF_PATIENTS
FROM KNOWN_MONTHS
JOIN ALIVE_PATIENTS
    ON ALIVE_PATIENTS.REGISTRATION_MONTH <= KNOWN_MONTHS.REF_MONTH
LEFT OUTER JOIN BP_ENCOUNTERS
    ON BP_ENCOUNTERS.patient_id = ALIVE_PATIENTS.patient_id
        AND BP_ENCOUNTER_MONTH <= KNOWN_MONTHS.REF_MONTH
        AND BP_ENCOUNTER_MONTH + interval '12 month'> KNOWN_MONTHS.REF_MONTH
GROUP BY KNOWN_MONTHS.REF_MONTH
ORDER BY KNOWN_MONTHS.REF_MONTH DESC;

KNOWN_MONTHS

This is a list of months known by the system. It can be obtained in a variety of ways, but can be derived from the months explicitly existing in the Database as in the example.

ALIVE_PATIENTS

This is a list of all patients that are alive. We are limiting our subquery to the columns that is relevant for this aggregation. Registration date is rounded by month as we’re doing a monthly analysis.

BP_ENCOUNTERS

This is a list of all BP readings taken for each patient based on the visit month.

There are two mutually exclusive categories for Patients Under Care:

• Newly registered: Patient registered in the past 3 months (not visualized).
• Patients under care registered before the past 3 months: Patients registered before the past 3 months.

There are 3 mutually exclusive categories for Patients under care registered before the past 3 months:

• Controlled BP: Patient registered before the past 3 months, with a BP reading <140/90 (systolic BP < 140 AND diastolic BP < 90) recorded at the patient's most recent visit within the past 3 months.
• Uncontrolled BP: Patient registered before the past 3 months, with a BP reading >=140/90 (systolic BP >= 140 OR diastolic BP >= 90) recorded at the patient's most recent visit within the past 3.
• No visit: Patient registered before the past 3 months, with no recorded visited in the past 3 months.

A patient was registered into the hypertension program in Sep-2023. They visited for blood pressure measures in Nov-2023 (BP controlled), Jan-2024 (BP not controlled), Feb-2024 (BP not controlled) and Mar-2024 (BP controlled).

Today is Apr-2024, this patient has visited twice in the past 3 months (Feb-2024 and Mar-2024) and is categorized in the controlled BP group at their latest visit (Mar-2024).

A patient was registered into the hypertension program in Sep-2023. They visited for blood pressure measures in Nov-2023 (BP controlled), Feb-2024 (BP not controlled) and Apr-2024 (BP not controlled). Today is Apr-2024, this patient has visited twice in the past 3 months (Feb-2024 and Apr-2024) and is uncontrolled BP at their latest visit (Apr-2024).

A patient was registered into the hypertension program in Sep-2023. They visited for blood pressure measures in Dec-2023 (BP controlled), Jan-2024 (BP not controlled). Today is Apr-2024, this patient had no visit in the past 3 months.

WITH
KNOWN_MONTHS AS (
    SELECT
        DISTINCT(DATE_TRUNC('month',created_at)) AS REF_MONTH
    FROM patients
),
ALIVE_PATIENTS AS (
    SELECT
        DATE_TRUNC('month',created_at) AS REGISTRATION_MONTH,
        id AS patient_id
    FROM patients
    WHERE PATIENT_STATUS <> 'dead'
),
BP_ENCOUNTERS AS (
    SELECT
        id,
        patient_id,
        systolic,
        diastolic,
        created_at  AS BP_ENCOUNTER_DATE,
        DATE_TRUNC('month',created_at)  AS BP_ENCOUNTER_MONTH
    FROM blood_pressures
),
LATEST_BP_BY_MONTH_AND_PATIENT AS (
    WITH MOST_RECENT_BP_ENCOUNTER AS (
        SELECT
            KNOWN_MONTHS.REF_MONTH,
            MOST_RECENT_BP_ENCOUNTER.patient_id,
            MAX(MOST_RECENT_BP_ENCOUNTER.created_at) AS MOST_RECENT_BP_DATE
        FROM blood_pressures MOST_RECENT_BP_ENCOUNTER
        JOIN KNOWN_MONTHS
            ON DATE_TRUNC('month', MOST_RECENT_BP_ENCOUNTER.created_at) <= KNOWN_MONTHS.REF_MONTH
            AND DATE_TRUNC('month',MOST_RECENT_BP_ENCOUNTER.created_at) + interval '12 month'> KNOWN_MONTHS.REF_MONTH
        GROUP BY KNOWN_MONTHS.REF_MONTH, patient_id)
    SELECT
        REF_MONTH, MOST_RECENT_BP_ENCOUNTER.patient_id,
        MAX(systolic) AS systolic,
        MAX(diastolic) AS diastolic,
        MAX(BP_ENCOUNTER_MONTH) AS BP_ENCOUNTER_MONTH
    FROM MOST_RECENT_BP_ENCOUNTER
    JOIN BP_ENCOUNTERS
        ON MOST_RECENT_BP_ENCOUNTER.MOST_RECENT_BP_DATE = BP_ENCOUNTERS.BP_ENCOUNTER_DATE
        AND MOST_RECENT_BP_ENCOUNTER.patient_id = BP_ENCOUNTERS.patient_id
    GROUP BY REF_MONTH, MOST_RECENT_BP_ENCOUNTER.patient_id
)
SELECT
    KNOWN_MONTHS.REF_MONTH,
    COUNT(*) AS NB_PATIENTS_UNDER_CARE,
    SUM(CASE WHEN ALIVE_PATIENTS.REGISTRATION_MONTH + interval '3 month' > KNOWN_MONTHS.REF_MONTH then 1 else 0 end ) AS NB_PATIENTS_NEWLY_REGISTERED,
    SUM(CASE WHEN ALIVE_PATIENTS.REGISTRATION_MONTH + interval '3 month' > KNOWN_MONTHS.REF_MONTH then 0 else 1 end ) AS NB_PATIENTS_UNDER_CARE_REGISTERED_BEFORE_THE_PAST_3_MONTHS,
    SUM(CASE
        WHEN ALIVE_PATIENTS.REGISTRATION_MONTH + interval '3 month' > KNOWN_MONTHS.REF_MONTH then 0
        WHEN LATEST_BP_BY_MONTH_AND_PATIENT.BP_ENCOUNTER_MONTH + interval '3 month' <= KNOWN_MONTHS.REF_MONTH then 1
        ELSE 0 END ) as NB_PATIENTS_NO_VISIT,
    SUM(CASE
        WHEN ALIVE_PATIENTS.REGISTRATION_MONTH + interval '3 month' > KNOWN_MONTHS.REF_MONTH then 0
        WHEN LATEST_BP_BY_MONTH_AND_PATIENT.BP_ENCOUNTER_MONTH + interval '3 month' <= KNOWN_MONTHS.REF_MONTH then 0
        WHEN systolic > 140 OR diastolic > 90 then 1
        ELSE 0 END ) aASs NB_PATIENTS_UNCONTROLLED,
    SUM(CASE
        WHEN ALIVE_PATIENTS.REGISTRATION_MONTH + interval '3 month' > KNOWN_MONTHS.REF_MONTH then 0
        WHEN LATEST_BP_BY_MONTH_AND_PATIENT.BP_ENCOUNTER_MONTH + interval '3 month' <= KNOWN_MONTHS.REF_MONTH then 0
        WHEN systolic > 140 OR diastolic > 90 then 0
        ELSE 1 END ) AS NB_PATIENTS_CONTROLLED
FROM KNOWN_MONTHS
JOIN ALIVE_PATIENTS
    ON ALIVE_PATIENTS.REGISTRATION_MONTH <= KNOWN_MONTHS.REF_MONTH
JOIN LATEST_BP_BY_MONTH_AND_PATIENT
    ON LATEST_BP_BY_MONTH_AND_PATIENT.patient_id = ALIVE_PATIENTS.patient_id
    AND LATEST_BP_BY_MONTH_AND_PATIENT.REF_MONTH = KNOWN_MONTHS.REF_MONTH
GROUP BY KNOWN_MONTHS.REF_MONTH
ORDER BY 1 DESC

KNOWN_MONTHS

This is a list of months known by the system. It can be obtained in a variety of ways, but can be derived from the months explicitly existing in the Database as in the example.

ALIVE_PATIENT

This is a list of all patients that are alive. We are limiting our subquery to the columns that is relevant for this aggregation. Registration date is rounded by month as we’re doing a monthly analysis.

BP_ENCOUNTERS

This is a list of all BP readings taken for each patient based on the visit month.

LATEST_BP_BY_MONTH_AND_PATIENT

This subquery returns the most recent BP reading for every patient for every Reference Month.

This is done by first taking the date of the latest BP Measurement in the past 12 months from a given Reference Month for a given patient.

SELECT
    KNOWN_MONTHS.REF_MONTH,
    MOST_RECENT_BP_ENCOUNTER.patient_id,
    MAX(MOST_RECENT_BP_ENCOUNTER.created_at) AS MOST_RECENT_BP_DATE
FROM blood_pressures MOST_RECENT_BP_ENCOUNTER
JOIN KNOWN_MONTHS
    ON DATE_TRUNC('month', MOST_RECENT_BP_ENCOUNTER.created_at) <= KNOWN_MONTHS.REF_MONTH
    AND DATE_TRUNC('month',MOST_RECENT_BP_ENCOUNTER.created_at) + interval '12 month'> KNOWN_MONTHS.REF_MONTH
GROUP BY KNOWN_MONTHS.REF_MONTH, patient_id

Using this result, we join it against BP_ENCOUNTERS to get the information we need.

The treatment cascade gives a view of how many estimated patients with hypertension in the region have been registered into the hypertension program, how many are under treatment for hypertension and, of those under treatment, how many have controlled blood pressure. The cascade should be constructed for the latest completed reference month.

The estimated patients with hypertension in the region can be found from STEPs survey data and entered into the system. The estimated number of people with hypertension cannot be inferred from the database.

*The values related to these figures are highlight in the previous data example tables with a colored circle:

• Cumulative registrations 🔵 [View data example] [View date example 2]
• Patients under care 🟣 [View data example] [View data example 2]
• Patients with BP controlled 🟢 [View data example (Scoll right)]

A patient with a scheduled visit date that has passed with no visit.

Consideration: For systems where scheduling a follow-up visit is not mandatory, an additional method is to mark patients overdue when their most recent visit is >30 days in the past.

Reports for overdue patients require the introduction of two new tables:

The number of patients that are overdue on the 1st of the month.

Example 1: Overdue at the start of the month

A patient has a scheduled visit on 5-Jan, and did not attend for care in January. This patient is counted as overdue at the start of the month for February.

Note: This patient became overdue on 6-Jan and will not be counted in Jan as they were not overdue on 1-Jan.

Example 2: ‘Removed from overdue list’ before the 1st of the month

A patient has a scheduled visit on 5-Jan, and did not attend for care in January. The patient was called on 15-Jan with an outcome ‘Refused to return’, part of the ‘Remove from overdue list’ outcomes. This patient is not counted as overdue at the start of the month for February.

The number of unique overdue patients that were called at least once during the month.

Patients called multiple times during the month are only counted once.

Example 1: Called during the month

A patient had a scheduled visit for 5-Jan and did not attend. They were called on 7-Jan. This patient is counted as overdue patient called in January.

Example 2: Multiple calls

A patient had a scheduled visit on 30-Jan, and did not attend. The patient was called on 8-Feb with a call result ‘Remind to call later’. The same patient was called again on 15-Feb with a call result ‘Agreed to visit’. This patient is counted as overdue patient called in Feb with a call result ‘Remind to call later’ (The first call during the month).

Overdue patients that were called and returned to care within 15 days.

Example 1: Returned within 15-days

A patient received a call on 1-Jan and attends for care on 16-Jan. This patient is counted as returned to care for January.

Example 2: Returned after 15-days

A patient received a call on 1-Jan and attends for care on 17-Jan. This patient is not counted as returned to care for January.

Example 3: Crosses into next month

A patient received a call on 31-Jan and attends for care on 5-Feb. This patient is counted as returned to care for January. (15-day window: 31-Jan to 15-Feb).

Example 4: Multiple calls

A patient received a call on 10-Jan and a second call on 20-Jan. Patient attends for care on 27-Jan. This patient is not counted as returned to care for January. (15-day window: 10-Jan to 26-Jan). The 15-day window begins from the first call during the month.

Example 5: Multiple calls crossing to next month

A patient received a call on 30-Jan and another call on 3-Feb. Patient attends for care on 5-Feb. This patient is counted as returned to care for both January and February. (15-day window for Jan: 30-Jan to 14-Feb. 15-day window for Feb: 3-Feb to 18-Feb). The first call during the month begins a 15-day window. The 15-day windows from different months can overlap.

WITH
-- Reference month based on query execution time
KNOWN_MONTHS AS (
  SELECT DATE_TRUNC('month', CURRENT_DATE) AS REF_MONTH
),

-- All alive patients with a valid phone number
ALIVE_PATIENTS_WITH_VALID_PHONE AS (
  SELECT
    PATIENT_ID,
    CONTACT_NUMBER
  FROM patients
  WHERE PATIENT_STATUS <> 'dead'
    AND CONTACT_NUMBER IS NOT NULL
    AND LENGTH(REGEXP_REPLACE(CONTACT_NUMBER, '[^0-9]', '', 'g')) >= 8
),

-- All BP encounters with truncated month
BP_ENCOUNTERS AS (
  SELECT
    PATIENT_ID,
    ENCOUNTER_DATE,
    DATE_TRUNC('month', ENCOUNTER_DATE) AS BP_ENCOUNTER_MONTH
  FROM bp_encounters
),

-- Most recent encounter per patient before REF_MONTH
RECENT_BP_ENCOUNTERS AS (
  SELECT DISTINCT ON (PATIENT_ID)
    PATIENT_ID,
    ENCOUNTER_DATE
  FROM bp_encounters, KNOWN_MONTHS
  WHERE ENCOUNTER_DATE < REF_MONTH
  ORDER BY PATIENT_ID, ENCOUNTER_DATE DESC
),

-- Most recent scheduled visit before REF_MONTH
RECENT_SCHEDULED_VISITS AS (
  SELECT DISTINCT ON (PATIENT_ID)
    PATIENT_ID,
    SCHEDULED_DATE
  FROM scheduled_visits, KNOWN_MONTHS
  WHERE SCHEDULED_DATE < REF_MONTH
  ORDER BY PATIENT_ID, SCHEDULED_DATE DESC
),

-- Most recent call result before REF_MONTH
RECENT_CALL_RESULTS AS (
  SELECT DISTINCT ON (PATIENT_ID)
    PATIENT_ID,
    RESULT_TYPE
  FROM call_results, KNOWN_MONTHS
  WHERE CALL_DATE < REF_MONTH
  ORDER BY PATIENT_ID, CALL_DATE DESC
),

-- Patients under care (seen in the past 12 months)
PATIENTS_UNDER_CARE AS (
  SELECT DISTINCT AP.PATIENT_ID
  FROM ALIVE_PATIENTS_WITH_VALID_PHONE AP
  LEFT JOIN BP_ENCOUNTERS BE ON BE.PATIENT_ID = AP.PATIENT_ID
  CROSS JOIN KNOWN_MONTHS
  WHERE BE.BP_ENCOUNTER_MONTH <= REF_MONTH
    AND BE.BP_ENCOUNTER_MONTH + INTERVAL '12 months' > REF_MONTH
),

-- Patients overdue as of 1st of the month
OVERDUE_ON_FIRST AS (
  SELECT
    AP.PATIENT_ID
  FROM ALIVE_PATIENTS_WITH_VALID_PHONE AP
  INNER JOIN PATIENTS_UNDER_CARE PUC ON AP.PATIENT_ID = PUC.PATIENT_ID
  INNER JOIN RECENT_SCHEDULED_VISITS RSV ON AP.PATIENT_ID = RSV.PATIENT_ID
  LEFT JOIN RECENT_BP_ENCOUNTERS RBE ON AP.PATIENT_ID = RBE.PATIENT_ID
  LEFT JOIN RECENT_CALL_RESULTS RCR ON AP.PATIENT_ID = RCR.PATIENT_ID
  WHERE (RCR.RESULT_TYPE IS NULL OR RCR.RESULT_TYPE NOT IN ('Remove from list'))
    AND (RBE.ENCOUNTER_DATE IS NULL OR RBE.ENCOUNTER_DATE < RSV.SCHEDULED_DATE)
),

-- First call during the month for each patient
FIRST_CALLS_IN_MONTH AS (
  SELECT DISTINCT ON (CR.PATIENT_ID)
    CR.PATIENT_ID,
    CR.CALL_DATE,
    CR.RESULT_TYPE,
    CR.CALL_DATE + INTERVAL '15 days' AS WINDOW_END_DATE
  FROM call_results CR
  INNER JOIN KNOWN_MONTHS KM ON TRUE
  WHERE CR.CALL_DATE >= KM.REF_MONTH
    AND CR.CALL_DATE < KM.REF_MONTH + INTERVAL '1 month'
  ORDER BY CR.PATIENT_ID, CR.CALL_DATE ASC
),

-- Patients called
CALLED_PATIENTS AS (
  SELECT DISTINCT FC.PATIENT_ID
  FROM FIRST_CALLS_IN_MONTH FC
  INNER JOIN ALIVE_PATIENTS_WITH_VALID_PHONE AP ON FC.PATIENT_ID = AP.PATIENT_ID
),

-- Patients who returned to care within 15-day window
RETURNED_TO_CARE AS (
  SELECT DISTINCT FC.PATIENT_ID
  FROM FIRST_CALLS_IN_MONTH FC
  INNER JOIN ALIVE_PATIENTS_WITH_VALID_PHONE AP ON FC.PATIENT_ID = AP.PATIENT_ID
  INNER JOIN bp_encounters BE ON BE.PATIENT_ID = FC.PATIENT_ID
  WHERE BE.ENCOUNTER_DATE BETWEEN FC.CALL_DATE AND FC.WINDOW_END_DATE
)

-- Final aggregation
SELECT
  KM.REF_MONTH AS REF_MONTH,
  COUNT(DISTINCT OOF.PATIENT_ID) AS OVERDUE_ON_FIRST,
  COUNT(DISTINCT CP.PATIENT_ID) AS OVERDUE_PATIENTS_CALLED,
  COUNT(DISTINCT RTC.PATIENT_ID) AS OVERDUE_RETURNED_TO_CARE
FROM KNOWN_MONTHS KM
LEFT JOIN OVERDUE_ON_FIRST OOF ON TRUE
LEFT JOIN CALLED_PATIENTS CP ON TRUE
LEFT JOIN RETURNED_TO_CARE RTC ON TRUE;

KNOWN_MONTHS

This is a list of months known by the system. It can be obtained in a variety of ways, but can be derived from the months explicitly existing in the database, as in the example.

ALIVE_PATIENTS_WITH_VALID_PHONE

This is a list of patients who are marked as alive and have a valid contact number. Only patients with numeric contact numbers of 8 digits or more are included. The registration date is truncated to the month level for consistency in monthly cohort analysis.

BP_ENCOUNTERS

This is a list of all BP readings taken for each patient based on the visit month.

RECENT_BP_ENCOUNTERS

This is a list of the most recent blood pressure encounter before the start of the reference month for each patient. It is used to determine whether the patient attended a visit after their last scheduled visit.

RECENT_SCHEDULED_VISITS

This is a list of the most recent scheduled visit before the start of the reference month for each patient. It is used to assess if a patient had a missed appointment.

RECENT_CALL_RESULTS

This is a list of the most recent call result recorded before the reference month. It helps exclude patients who were removed from the follow-up list or marked as deceased based on call outcomes.

PATIENTS_UNDER_CARE

This is a list of patients who had at least one blood pressure encounter within the past 12 months from the reference month. These patients are considered “under care” for the purposes of reporting and are not classified as lost to follow-up.

OVERDUE_ON_FIRST

This identifies patients who were overdue as of the 1st of the reference month. Patients are considered overdue if:

• They had a scheduled visit before the month started,
• They either did not attend any visit after that scheduled date or had no recent encounter,
• They were not marked as “Removed from overdue list” via call outcomes
• They are not lost to follow-up or dead.

FIRST_CALLS_IN_MONTH This returns the first call per patient within the reference month, along with the outcome and a calculated 15-day follow-up window.

CALLED_PATIENTS This includes patients who had at least one valid call during the reference month. Only the first call is used to count a patient as called.

RETURNED_TO_CARE This identifies patients who returned for a BP visit within 15 days (inclusive) of their first call during the reference month, linking each patient’s first call with subsequent BP encounter to determine if they returned.

Numerator: overdue_on_first

Denominator : patients_under_care

The number of overdue patients varies during the month, making it hard to track consistently. Fixing the value at the start of each month allows trend analysis over time. Short-term overdue cases that resolve quickly don’t affect this fixed value, giving staff a clearer view while day-to-day follow-up continues.

Numerator: overdue_patients_called

Denominator: overdue_on_first

This indicator is an estimate. Patients overdue at the start of the month need to be called. Using this as the base, facilities can see a fixed, “minimum”, number of overdue patients that need calling during the month.

The number of patients called (numerator) may exceed the number overdue at the start of the month (denominator). This happens because patients who became overdue during the month and were called are also counted. For context, calling 100% of overdue patients during the month is uncommon. This number is capped at 100%.

Most patients are called only once during the month. Using the first call during the month ensures the indicators are consistent.

Numerator: overdue_returned_to_care

Denominator: overdue_patients_called

Most patients are called only once during the month. The 15-day window begins on the first call to keep indicators consistent.

Only overdue patients can be called: Calling lists should only display patients that are overdue; alternatively, calls made specifically for overdue calling should be encoded to identify them from other calls being made to patients.

Death date when call outcome is ‘Died’: When ‘Remove from overdue list’ reason ‘Died’ is selected as a call outcome the patient death_date is updated. Where a death date exists patients are filtered from dashboard figures.

In order to aggregate by region/state, queries can be modified easily to take this in account by adding the correct level of aggregation in:

• Patient subqueries
• Group by clause of the main query

Depending on the size of the different tables, these queries may be too resource intensive to run in real time, each time a user views the HEARTS360 dashboard.

It is a good practice to cache the results and refresh the tables daily. For example, using Materialized Views that refresh once a day. Caching can also be done at application level, or through a variety of other mechanisms.

After these queries have been adapted to a system, it is a good practice to check that values obtained through the different queries are consistent:

• The same numbers obtained from different queries should be identical:

  • CUMULATIVE_NUMBER_OF_PATIENTS should be the same value from the two queries that produce it
  • NB_PATIENTS_UNDER_CARE should be the same value from the two queries that produce it

• The following queries should total:

  • NB_PATIENTS_UNDER_CARE = NB_PATIENTS_NEWLY_REGISTERED + NB_PATIENTS_CONTROLLED + NB_PATIENTS_UNCONTROLLED + NB_PATIENTS_NO_VISIT
  • NB_PATIENTS_UNDER_CARE_REGISTERED_BEFORE_PAST_3_MONTHS = NB_PATIENTS_UNDER_CARE - NB_PATIENTS_NEWLY_REGISTERED
  • CUMULATIVE_NUMBER_OF_PATIENTS = NB_PATIENTS_LOST_TO_FOLLOW_UP + NB_PATIENTS_UNDER_CARE

• Cumulative indicators should increase over time:

  • Cumulative number of dead patients
  • Cumulative number of registered patients

• Month to month verification

  • NB_PATIENTS_NEWLY_REGISTERED of month X = (CUMULATIVE_NUMBER_OF_PATIENTS of month X) - (CUMULATIVE_NUMBER_OF_PATIENTS of month X -3)

If your PATIENTS and EVENTS tables are structured similar to the database example, it should be possible to adapt the queries by changing only the subqueries.

This is the easiest way to produce valid queries that will work for your data.

The generic dashboard is licensed under an MIT License. Please take this code and use it for your own project in any way.

Copyright 2023 Resolve to Save Lives

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.