materialized_patient_summaries

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

uuid

2147483647

√

null

recorded_at

timestamp

29,6

√

null

full_name

varchar

2147483647

√

null

latest_bp_passport_id

uuid

2147483647

√

null

latest_bp_passport_identifier

varchar

2147483647

√

null

current_age

numeric

√

null

gender

varchar

2147483647

√

null

status

varchar

2147483647

√

null

latest_phone_number

varchar

2147483647

√

null

village_or_colony

varchar

2147483647

√

null

street_address

varchar

2147483647

√

null

district

varchar

2147483647

√

null

zone

varchar

2147483647

√

null

state

varchar

2147483647

√

null

assigned_facility_name

varchar

2147483647

√

null

assigned_facility_type

varchar

2147483647

√

null

assigned_facility_state

varchar

2147483647

√

null

assigned_facility_district

varchar

2147483647

√

null

registration_facility_name

varchar

2147483647

√

null

registration_facility_type

varchar

2147483647

√

null

registration_facility_state

varchar

2147483647

√

null

registration_facility_district

varchar

2147483647

√

null

hypertension

text

2147483647

√

null

diabetes

text

2147483647

√

null

days_overdue

float8

17,17

√

null

next_scheduled_appointment_id

uuid

2147483647

√

null

next_scheduled_appointment_scheduled_date

date

√

null

next_scheduled_appointment_status

varchar

2147483647

√

null

next_scheduled_appointment_remind_on

date

√

null

next_scheduled_appointment_facility_id

uuid

2147483647

√

null

next_scheduled_appointment_facility_name

varchar

2147483647

√

null

next_scheduled_appointment_facility_type

varchar

2147483647

√

null

next_scheduled_appointment_district

varchar

2147483647

√

null

next_scheduled_appointment_state

varchar

2147483647

√

null

latest_blood_pressure_1_id

uuid

2147483647

√

null

latest_blood_pressure_1_recorded_at

timestamp

29,6

√

null

latest_blood_pressure_1_systolic

int4

√

null

latest_blood_pressure_1_diastolic

int4

√

null

latest_blood_pressure_1_facility_name

varchar

2147483647

√

null

latest_blood_pressure_1_facility_type

varchar

2147483647

√

null

latest_blood_pressure_1_district

varchar

2147483647

√

null

latest_blood_pressure_1_state

varchar

2147483647

√

null

latest_blood_pressure_1_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_pressure_1_follow_up_date

date

√

null

latest_blood_pressure_1_follow_up_days

int4

√

null

latest_blood_pressure_1_medication_updated

bool

√

null

latest_blood_pressure_1_prescription_drug_1_name

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_1_dosage

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_2_name

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_2_dosage

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_3_name

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_3_dosage

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_4_name

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_4_dosage

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_5_name

varchar

2147483647

√

null

latest_blood_pressure_1_prescription_drug_5_dosage

varchar

2147483647

√

null

latest_blood_pressure_1_other_prescription_drugs

text

2147483647

√

null

latest_blood_pressure_2_id

uuid

2147483647

√

null

latest_blood_pressure_2_recorded_at

timestamp

29,6

√

null

latest_blood_pressure_2_systolic

int4

√

null

latest_blood_pressure_2_diastolic

int4

√

null

latest_blood_pressure_2_facility_name

varchar

2147483647

√

null

latest_blood_pressure_2_facility_type

varchar

2147483647

√

null

latest_blood_pressure_2_district

varchar

2147483647

√

null

latest_blood_pressure_2_state

varchar

2147483647

√

null

latest_blood_pressure_2_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_pressure_2_follow_up_date

date

√

null

latest_blood_pressure_2_follow_up_days

int4

√

null

latest_blood_pressure_2_medication_updated

bool

√

null

latest_blood_pressure_2_prescription_drug_1_name

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_1_dosage

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_2_name

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_2_dosage

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_3_name

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_3_dosage

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_4_name

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_4_dosage

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_5_name

varchar

2147483647

√

null

latest_blood_pressure_2_prescription_drug_5_dosage

varchar

2147483647

√

null

latest_blood_pressure_2_other_prescription_drugs

text

2147483647

√

null

latest_blood_pressure_3_id

uuid

2147483647

√

null

latest_blood_pressure_3_recorded_at

timestamp

29,6

√

null

latest_blood_pressure_3_systolic

int4

√

null

latest_blood_pressure_3_diastolic

int4

√

null

latest_blood_pressure_3_facility_name

varchar

2147483647

√

null

latest_blood_pressure_3_facility_type

varchar

2147483647

√

null

latest_blood_pressure_3_district

varchar

2147483647

√

null

latest_blood_pressure_3_state

varchar

2147483647

√

null

latest_blood_pressure_3_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_pressure_3_follow_up_date

date

√

null

latest_blood_pressure_3_follow_up_days

int4

√

null

latest_blood_pressure_3_medication_updated

bool

√

null

latest_blood_pressure_3_prescription_drug_1_name

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_1_dosage

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_2_name

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_2_dosage

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_3_name

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_3_dosage

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_4_name

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_4_dosage

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_5_name

varchar

2147483647

√

null

latest_blood_pressure_3_prescription_drug_5_dosage

varchar

2147483647

√

null

latest_blood_pressure_3_other_prescription_drugs

text

2147483647

√

null

latest_blood_sugar_1_id

uuid

2147483647

√

null

latest_blood_sugar_1_recorded_at

timestamp

29,6

√

null

latest_blood_sugar_1_blood_sugar_type

varchar

2147483647

√

null

latest_blood_sugar_1_blood_sugar_value

numeric

√

null

latest_blood_sugar_1_facility_name

varchar

2147483647

√

null

latest_blood_sugar_1_facility_type

varchar

2147483647

√

null

latest_blood_sugar_1_district

varchar

2147483647

√

null

latest_blood_sugar_1_state

varchar

2147483647

√

null

latest_blood_sugar_1_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_sugar_1_follow_up_date

date

√

null

latest_blood_sugar_1_follow_up_days

int4

√

null

latest_blood_sugar_2_id

uuid

2147483647

√

null

latest_blood_sugar_2_recorded_at

timestamp

29,6

√

null

latest_blood_sugar_2_blood_sugar_type

varchar

2147483647

√

null

latest_blood_sugar_2_blood_sugar_value

numeric

√

null

latest_blood_sugar_2_facility_name

varchar

2147483647

√

null

latest_blood_sugar_2_facility_type

varchar

2147483647

√

null

latest_blood_sugar_2_district

varchar

2147483647

√

null

latest_blood_sugar_2_state

varchar

2147483647

√

null

latest_blood_sugar_2_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_sugar_2_follow_up_date

date

√

null

latest_blood_sugar_2_follow_up_days

int4

√

null

latest_blood_sugar_3_id

uuid

2147483647

√

null

latest_blood_sugar_3_recorded_at

timestamp

29,6

√

null

latest_blood_sugar_3_blood_sugar_type

varchar

2147483647

√

null

latest_blood_sugar_3_blood_sugar_value

numeric

√

null

latest_blood_sugar_3_facility_name

varchar

2147483647

√

null

latest_blood_sugar_3_facility_type

varchar

2147483647

√

null

latest_blood_sugar_3_district

varchar

2147483647

√

null

latest_blood_sugar_3_state

varchar

2147483647

√

null

latest_blood_sugar_3_follow_up_facility_name

varchar

2147483647

√

null

latest_blood_sugar_3_follow_up_date

date

√

null

latest_blood_sugar_3_follow_up_days

int4

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH latest_bp_passport AS (
         SELECT DISTINCT ON (patient_business_identifiers.patient_id) patient_business_identifiers.id,
            patient_business_identifiers.identifier,
            patient_business_identifiers.identifier_type,
            patient_business_identifiers.patient_id,
            patient_business_identifiers.metadata_version,
            patient_business_identifiers.metadata,
            patient_business_identifiers.device_created_at,
            patient_business_identifiers.device_updated_at,
            patient_business_identifiers.deleted_at,
            patient_business_identifiers.created_at,
            patient_business_identifiers.updated_at
           FROM patient_business_identifiers
          WHERE (((patient_business_identifiers.identifier_type)::text = 'simple_bp_passport'::text) AND (patient_business_identifiers.deleted_at IS NULL))
          ORDER BY patient_business_identifiers.patient_id, patient_business_identifiers.device_created_at DESC
        ), latest_phone_number AS (
         SELECT DISTINCT ON (patient_phone_numbers.patient_id) patient_phone_numbers.id,
            patient_phone_numbers.number,
            patient_phone_numbers.phone_type,
            patient_phone_numbers.active,
            patient_phone_numbers.created_at,
            patient_phone_numbers.updated_at,
            patient_phone_numbers.patient_id,
            patient_phone_numbers.device_created_at,
            patient_phone_numbers.device_updated_at,
            patient_phone_numbers.deleted_at,
            patient_phone_numbers.dnd_status
           FROM patient_phone_numbers
          WHERE (patient_phone_numbers.deleted_at IS NULL)
          ORDER BY patient_phone_numbers.patient_id, patient_phone_numbers.device_created_at DESC
        ), latest_medical_history AS (
         SELECT DISTINCT ON (medical_histories.patient_id) medical_histories.id,
            medical_histories.patient_id,
            medical_histories.prior_heart_attack_boolean,
            medical_histories.prior_stroke_boolean,
            medical_histories.chronic_kidney_disease_boolean,
            medical_histories.receiving_treatment_for_hypertension_boolean,
            medical_histories.diabetes_boolean,
            medical_histories.device_created_at,
            medical_histories.device_updated_at,
            medical_histories.created_at,
            medical_histories.updated_at,
            medical_histories.diagnosed_with_hypertension_boolean,
            medical_histories.prior_heart_attack,
            medical_histories.prior_stroke,
            medical_histories.chronic_kidney_disease,
            medical_histories.receiving_treatment_for_hypertension,
            medical_histories.diabetes,
            medical_histories.diagnosed_with_hypertension,
            medical_histories.deleted_at,
            medical_histories.user_id,
            medical_histories.hypertension,
            medical_histories.receiving_treatment_for_diabetes,
            medical_histories.smoking,
            medical_histories.cholesterol
           FROM medical_histories
          WHERE (medical_histories.deleted_at IS NULL)
        ), ranked_prescription_drugs AS (
         SELECT bp.id AS bp_id,
            array_agg(ARRAY[prescription_drugs.name, prescription_drugs.dosage] ORDER BY prescription_drugs.is_protocol_drug DESC, prescription_drugs.name, prescription_drugs.device_created_at DESC) AS blood_pressure_drugs,
            array_agg((((prescription_drugs.name)::text || '-'::text) || (prescription_drugs.dosage)::text) ORDER BY prescription_drugs.is_protocol_drug DESC, prescription_drugs.name, prescription_drugs.device_created_at DESC) AS drug_strings
           FROM (blood_pressures bp
             JOIN prescription_drugs ON (((prescription_drugs.patient_id = bp.patient_id) AND (date(prescription_drugs.device_created_at) <= date(bp.recorded_at)) AND ((prescription_drugs.is_deleted IS FALSE) OR ((prescription_drugs.is_deleted IS TRUE) AND (date(prescription_drugs.device_updated_at) > date(bp.recorded_at)))))))
          WHERE ((bp.deleted_at IS NULL) AND (prescription_drugs.deleted_at IS NULL))
          GROUP BY bp.id
        ), blood_pressure_follow_up AS (
         SELECT DISTINCT ON (bp.patient_id, (date(bp.recorded_at))) bp.id AS bp_id,
            appointments.id,
            appointments.patient_id,
            appointments.facility_id,
            appointments.scheduled_date,
            appointments.status,
            appointments.cancel_reason,
            appointments.device_created_at,
            appointments.device_updated_at,
            appointments.created_at,
            appointments.updated_at,
            appointments.remind_on,
            appointments.agreed_to_visit,
            appointments.deleted_at,
            appointments.appointment_type,
            appointments.user_id,
            appointments.creation_facility_id
           FROM (blood_pressures bp
             JOIN appointments ON (((appointments.patient_id = bp.patient_id) AND (date(appointments.device_created_at) = date(bp.recorded_at)))))
          ORDER BY bp.patient_id, (date(bp.recorded_at)), appointments.device_created_at DESC
        ), blood_sugar_follow_up AS (
         SELECT DISTINCT ON (bs.patient_id, (date(bs.recorded_at))) bs.id AS bs_id,
            appointments.id,
            appointments.patient_id,
            appointments.facility_id,
            appointments.scheduled_date,
            appointments.status,
            appointments.cancel_reason,
            appointments.device_created_at,
            appointments.device_updated_at,
            appointments.created_at,
            appointments.updated_at,
            appointments.remind_on,
            appointments.agreed_to_visit,
            appointments.deleted_at,
            appointments.appointment_type,
            appointments.user_id,
            appointments.creation_facility_id
           FROM (blood_sugars bs
             JOIN appointments ON (((appointments.patient_id = bs.patient_id) AND (date(appointments.device_created_at) = date(bs.recorded_at)))))
          ORDER BY bs.patient_id, (date(bs.recorded_at)), appointments.device_created_at DESC
        ), ranked_blood_pressures AS (
         SELECT bp.id,
            bp.patient_id,
            bp.recorded_at,
            bp.systolic,
            bp.diastolic,
            f.name AS facility_name,
            f.facility_type,
            f.district,
            f.state,
            follow_up_facility.name AS follow_up_facility_name,
            a.scheduled_date AS follow_up_date,
            (GREATEST((0)::double precision, date_part('day'::text, ((a.scheduled_date)::timestamp without time zone - date_trunc('day'::text, a.device_created_at)))))::integer AS follow_up_days,
            bp_drugs.blood_pressure_drugs[1][1] AS prescription_drug_1_name,
            bp_drugs.blood_pressure_drugs[1][2] AS prescription_drug_1_dosage,
            bp_drugs.blood_pressure_drugs[2][1] AS prescription_drug_2_name,
            bp_drugs.blood_pressure_drugs[2][2] AS prescription_drug_2_dosage,
            bp_drugs.blood_pressure_drugs[3][1] AS prescription_drug_3_name,
            bp_drugs.blood_pressure_drugs[3][2] AS prescription_drug_3_dosage,
            bp_drugs.blood_pressure_drugs[4][1] AS prescription_drug_4_name,
            bp_drugs.blood_pressure_drugs[4][2] AS prescription_drug_4_dosage,
            bp_drugs.blood_pressure_drugs[5][1] AS prescription_drug_5_name,
            bp_drugs.blood_pressure_drugs[5][2] AS prescription_drug_5_dosage,
            ( SELECT string_agg(value.value, ', '::text) AS string_agg
                   FROM unnest(bp_drugs.drug_strings[6:]) value(value)) AS other_prescription_drugs,
            ( SELECT string_agg(value.value, ', '::text) AS string_agg
                   FROM unnest(bp_drugs.drug_strings) value(value)) AS all_prescription_drugs,
            rank() OVER (PARTITION BY bp.patient_id ORDER BY bp.recorded_at DESC, bp.id) AS rank
           FROM ((((blood_pressures bp
             LEFT JOIN facilities f ON ((bp.facility_id = f.id)))
             LEFT JOIN ranked_prescription_drugs bp_drugs ON ((bp.id = bp_drugs.bp_id)))
             LEFT JOIN blood_pressure_follow_up a ON ((a.bp_id = bp.id)))
             LEFT JOIN facilities follow_up_facility ON ((follow_up_facility.id = a.facility_id)))
          WHERE ((bp.deleted_at IS NULL) AND (a.deleted_at IS NULL))
        ), latest_blood_pressures AS (
         SELECT latest_blood_pressure_1.patient_id,
            latest_blood_pressure_1.id AS latest_blood_pressure_1_id,
            latest_blood_pressure_1.recorded_at AS latest_blood_pressure_1_recorded_at,
            latest_blood_pressure_1.systolic AS latest_blood_pressure_1_systolic,
            latest_blood_pressure_1.diastolic AS latest_blood_pressure_1_diastolic,
            latest_blood_pressure_1.facility_name AS latest_blood_pressure_1_facility_name,
            latest_blood_pressure_1.facility_type AS latest_blood_pressure_1_facility_type,
            latest_blood_pressure_1.district AS latest_blood_pressure_1_district,
            latest_blood_pressure_1.state AS latest_blood_pressure_1_state,
            latest_blood_pressure_1.follow_up_facility_name AS latest_blood_pressure_1_follow_up_facility_name,
            latest_blood_pressure_1.follow_up_date AS latest_blood_pressure_1_follow_up_date,
            latest_blood_pressure_1.follow_up_days AS latest_blood_pressure_1_follow_up_days,
            (latest_blood_pressure_1.all_prescription_drugs <> latest_blood_pressure_2.all_prescription_drugs) AS latest_blood_pressure_1_medication_updated,
            latest_blood_pressure_1.prescription_drug_1_name AS latest_blood_pressure_1_prescription_drug_1_name,
            latest_blood_pressure_1.prescription_drug_1_dosage AS latest_blood_pressure_1_prescription_drug_1_dosage,
            latest_blood_pressure_1.prescription_drug_2_name AS latest_blood_pressure_1_prescription_drug_2_name,
            latest_blood_pressure_1.prescription_drug_2_dosage AS latest_blood_pressure_1_prescription_drug_2_dosage,
            latest_blood_pressure_1.prescription_drug_3_name AS latest_blood_pressure_1_prescription_drug_3_name,
            latest_blood_pressure_1.prescription_drug_3_dosage AS latest_blood_pressure_1_prescription_drug_3_dosage,
            latest_blood_pressure_1.prescription_drug_4_name AS latest_blood_pressure_1_prescription_drug_4_name,
            latest_blood_pressure_1.prescription_drug_4_dosage AS latest_blood_pressure_1_prescription_drug_4_dosage,
            latest_blood_pressure_1.prescription_drug_5_name AS latest_blood_pressure_1_prescription_drug_5_name,
            latest_blood_pressure_1.prescription_drug_5_dosage AS latest_blood_pressure_1_prescription_drug_5_dosage,
            latest_blood_pressure_1.other_prescription_drugs AS latest_blood_pressure_1_other_prescription_drugs,
            latest_blood_pressure_2.id AS latest_blood_pressure_2_id,
            latest_blood_pressure_2.recorded_at AS latest_blood_pressure_2_recorded_at,
            latest_blood_pressure_2.systolic AS latest_blood_pressure_2_systolic,
            latest_blood_pressure_2.diastolic AS latest_blood_pressure_2_diastolic,
            latest_blood_pressure_2.facility_name AS latest_blood_pressure_2_facility_name,
            latest_blood_pressure_2.facility_type AS latest_blood_pressure_2_facility_type,
            latest_blood_pressure_2.district AS latest_blood_pressure_2_district,
            latest_blood_pressure_2.state AS latest_blood_pressure_2_state,
            latest_blood_pressure_2.follow_up_facility_name AS latest_blood_pressure_2_follow_up_facility_name,
            latest_blood_pressure_2.follow_up_date AS latest_blood_pressure_2_follow_up_date,
            latest_blood_pressure_2.follow_up_days AS latest_blood_pressure_2_follow_up_days,
            (latest_blood_pressure_2.all_prescription_drugs <> latest_blood_pressure_3.all_prescription_drugs) AS latest_blood_pressure_2_medication_updated,
            latest_blood_pressure_2.prescription_drug_1_name AS latest_blood_pressure_2_prescription_drug_1_name,
            latest_blood_pressure_2.prescription_drug_1_dosage AS latest_blood_pressure_2_prescription_drug_1_dosage,
            latest_blood_pressure_2.prescription_drug_2_name AS latest_blood_pressure_2_prescription_drug_2_name,
            latest_blood_pressure_2.prescription_drug_2_dosage AS latest_blood_pressure_2_prescription_drug_2_dosage,
            latest_blood_pressure_2.prescription_drug_3_name AS latest_blood_pressure_2_prescription_drug_3_name,
            latest_blood_pressure_2.prescription_drug_3_dosage AS latest_blood_pressure_2_prescription_drug_3_dosage,
            latest_blood_pressure_2.prescription_drug_4_name AS latest_blood_pressure_2_prescription_drug_4_name,
            latest_blood_pressure_2.prescription_drug_4_dosage AS latest_blood_pressure_2_prescription_drug_4_dosage,
            latest_blood_pressure_2.prescription_drug_5_name AS latest_blood_pressure_2_prescription_drug_5_name,
            latest_blood_pressure_2.prescription_drug_5_dosage AS latest_blood_pressure_2_prescription_drug_5_dosage,
            latest_blood_pressure_2.other_prescription_drugs AS latest_blood_pressure_2_other_prescription_drugs,
            latest_blood_pressure_3.id AS latest_blood_pressure_3_id,
            latest_blood_pressure_3.recorded_at AS latest_blood_pressure_3_recorded_at,
            latest_blood_pressure_3.systolic AS latest_blood_pressure_3_systolic,
            latest_blood_pressure_3.diastolic AS latest_blood_pressure_3_diastolic,
            latest_blood_pressure_3.facility_name AS latest_blood_pressure_3_facility_name,
            latest_blood_pressure_3.facility_type AS latest_blood_pressure_3_facility_type,
            latest_blood_pressure_3.district AS latest_blood_pressure_3_district,
            latest_blood_pressure_3.state AS latest_blood_pressure_3_state,
            latest_blood_pressure_3.follow_up_facility_name AS latest_blood_pressure_3_follow_up_facility_name,
            latest_blood_pressure_3.follow_up_date AS latest_blood_pressure_3_follow_up_date,
            latest_blood_pressure_3.follow_up_days AS latest_blood_pressure_3_follow_up_days,
            (latest_blood_pressure_3.all_prescription_drugs <> latest_blood_pressure_4.all_prescription_drugs) AS latest_blood_pressure_3_medication_updated,
            latest_blood_pressure_3.prescription_drug_1_name AS latest_blood_pressure_3_prescription_drug_1_name,
            latest_blood_pressure_3.prescription_drug_1_dosage AS latest_blood_pressure_3_prescription_drug_1_dosage,
            latest_blood_pressure_3.prescription_drug_2_name AS latest_blood_pressure_3_prescription_drug_2_name,
            latest_blood_pressure_3.prescription_drug_2_dosage AS latest_blood_pressure_3_prescription_drug_2_dosage,
            latest_blood_pressure_3.prescription_drug_3_name AS latest_blood_pressure_3_prescription_drug_3_name,
            latest_blood_pressure_3.prescription_drug_3_dosage AS latest_blood_pressure_3_prescription_drug_3_dosage,
            latest_blood_pressure_3.prescription_drug_4_name AS latest_blood_pressure_3_prescription_drug_4_name,
            latest_blood_pressure_3.prescription_drug_4_dosage AS latest_blood_pressure_3_prescription_drug_4_dosage,
            latest_blood_pressure_3.prescription_drug_5_name AS latest_blood_pressure_3_prescription_drug_5_name,
            latest_blood_pressure_3.prescription_drug_5_dosage AS latest_blood_pressure_3_prescription_drug_5_dosage,
            latest_blood_pressure_3.other_prescription_drugs AS latest_blood_pressure_3_other_prescription_drugs
           FROM (((ranked_blood_pressures latest_blood_pressure_1
             LEFT JOIN ranked_blood_pressures latest_blood_pressure_2 ON (((latest_blood_pressure_2.patient_id = latest_blood_pressure_1.patient_id) AND (latest_blood_pressure_2.rank = 2))))
             LEFT JOIN ranked_blood_pressures latest_blood_pressure_3 ON (((latest_blood_pressure_3.patient_id = latest_blood_pressure_1.patient_id) AND (latest_blood_pressure_3.rank = 3))))
             LEFT JOIN ranked_blood_pressures latest_blood_pressure_4 ON (((latest_blood_pressure_4.patient_id = latest_blood_pressure_1.patient_id) AND (latest_blood_pressure_4.rank = 4))))
          WHERE (latest_blood_pressure_1.rank = 1)
        ), ranked_blood_sugars AS (
         SELECT bs.id,
            bs.patient_id,
            bs.recorded_at,
            bs.blood_sugar_type,
            bs.blood_sugar_value,
            f.name AS facility_name,
            f.facility_type,
            f.district,
            f.state,
            follow_up_facility.name AS follow_up_facility_name,
            a.scheduled_date AS follow_up_date,
            (GREATEST((0)::double precision, date_part('day'::text, ((a.scheduled_date)::timestamp without time zone - date_trunc('day'::text, a.device_created_at)))))::integer AS follow_up_days,
            rank() OVER (PARTITION BY bs.patient_id ORDER BY bs.recorded_at DESC, bs.id) AS rank
           FROM (((blood_sugars bs
             LEFT JOIN facilities f ON ((bs.facility_id = f.id)))
             LEFT JOIN blood_sugar_follow_up a ON ((a.bs_id = bs.id)))
             LEFT JOIN facilities follow_up_facility ON ((follow_up_facility.id = a.facility_id)))
          WHERE ((bs.deleted_at IS NULL) AND (a.deleted_at IS NULL))
        ), latest_blood_sugars AS (
         SELECT latest_blood_sugar_1.patient_id,
            latest_blood_sugar_1.id AS latest_blood_sugar_1_id,
            latest_blood_sugar_1.recorded_at AS latest_blood_sugar_1_recorded_at,
            latest_blood_sugar_1.blood_sugar_type AS latest_blood_sugar_1_blood_sugar_type,
            latest_blood_sugar_1.blood_sugar_value AS latest_blood_sugar_1_blood_sugar_value,
            latest_blood_sugar_1.facility_name AS latest_blood_sugar_1_facility_name,
            latest_blood_sugar_1.facility_type AS latest_blood_sugar_1_facility_type,
            latest_blood_sugar_1.district AS latest_blood_sugar_1_district,
            latest_blood_sugar_1.state AS latest_blood_sugar_1_state,
            latest_blood_sugar_1.follow_up_facility_name AS latest_blood_sugar_1_follow_up_facility_name,
            latest_blood_sugar_1.follow_up_date AS latest_blood_sugar_1_follow_up_date,
            latest_blood_sugar_1.follow_up_days AS latest_blood_sugar_1_follow_up_days,
            latest_blood_sugar_2.id AS latest_blood_sugar_2_id,
            latest_blood_sugar_2.recorded_at AS latest_blood_sugar_2_recorded_at,
            latest_blood_sugar_2.blood_sugar_type AS latest_blood_sugar_2_blood_sugar_type,
            latest_blood_sugar_2.blood_sugar_value AS latest_blood_sugar_2_blood_sugar_value,
            latest_blood_sugar_2.facility_name AS latest_blood_sugar_2_facility_name,
            latest_blood_sugar_2.facility_type AS latest_blood_sugar_2_facility_type,
            latest_blood_sugar_2.district AS latest_blood_sugar_2_district,
            latest_blood_sugar_2.state AS latest_blood_sugar_2_state,
            latest_blood_sugar_2.follow_up_facility_name AS latest_blood_sugar_2_follow_up_facility_name,
            latest_blood_sugar_2.follow_up_date AS latest_blood_sugar_2_follow_up_date,
            latest_blood_sugar_2.follow_up_days AS latest_blood_sugar_2_follow_up_days,
            latest_blood_sugar_3.id AS latest_blood_sugar_3_id,
            latest_blood_sugar_3.recorded_at AS latest_blood_sugar_3_recorded_at,
            latest_blood_sugar_3.blood_sugar_type AS latest_blood_sugar_3_blood_sugar_type,
            latest_blood_sugar_3.blood_sugar_value AS latest_blood_sugar_3_blood_sugar_value,
            latest_blood_sugar_3.facility_name AS latest_blood_sugar_3_facility_name,
            latest_blood_sugar_3.facility_type AS latest_blood_sugar_3_facility_type,
            latest_blood_sugar_3.district AS latest_blood_sugar_3_district,
            latest_blood_sugar_3.state AS latest_blood_sugar_3_state,
            latest_blood_sugar_3.follow_up_facility_name AS latest_blood_sugar_3_follow_up_facility_name,
            latest_blood_sugar_3.follow_up_date AS latest_blood_sugar_3_follow_up_date,
            latest_blood_sugar_3.follow_up_days AS latest_blood_sugar_3_follow_up_days
           FROM ((ranked_blood_sugars latest_blood_sugar_1
             LEFT JOIN ranked_blood_sugars latest_blood_sugar_2 ON (((latest_blood_sugar_2.patient_id = latest_blood_sugar_1.patient_id) AND (latest_blood_sugar_2.rank = 2))))
             LEFT JOIN ranked_blood_sugars latest_blood_sugar_3 ON (((latest_blood_sugar_3.patient_id = latest_blood_sugar_1.patient_id) AND (latest_blood_sugar_3.rank = 3))))
          WHERE (latest_blood_sugar_1.rank = 1)
        ), next_scheduled_appointment AS (
         SELECT DISTINCT ON (appointments.patient_id) appointments.id,
            appointments.patient_id,
            appointments.facility_id,
            appointments.scheduled_date,
            appointments.status,
            appointments.cancel_reason,
            appointments.device_created_at,
            appointments.device_updated_at,
            appointments.created_at,
            appointments.updated_at,
            appointments.remind_on,
            appointments.agreed_to_visit,
            appointments.deleted_at,
            appointments.appointment_type,
            appointments.user_id,
            appointments.creation_facility_id,
            f.id AS appointment_facility_id,
            f.name AS appointment_facility_name,
            f.facility_type AS appointment_facility_type,
            f.district AS appointment_district,
            f.state AS appointment_state
           FROM (appointments
             LEFT JOIN facilities f ON (((f.id = appointments.facility_id) AND ((appointments.status)::text = 'scheduled'::text))))
          WHERE (appointments.deleted_at IS NULL)
          ORDER BY appointments.patient_id, appointments.device_created_at DESC
        )
 SELECT DISTINCT ON (p.id) p.id,
    p.recorded_at,
    p.full_name,
    latest_bp_passport.id AS latest_bp_passport_id,
    latest_bp_passport.identifier AS latest_bp_passport_identifier,
    EXTRACT(year FROM COALESCE(age((p.date_of_birth)::timestamp with time zone), (make_interval(years => p.age) + age(p.age_updated_at)))) AS current_age,
    p.gender,
    p.status,
    latest_phone_number.number AS latest_phone_number,
    addresses.village_or_colony,
    addresses.street_address,
    addresses.district,
    addresses.zone,
    addresses.state,
    assigned_facility.name AS assigned_facility_name,
    assigned_facility.facility_type AS assigned_facility_type,
    assigned_facility.state AS assigned_facility_state,
    assigned_facility.district AS assigned_facility_district,
    registration_facility.name AS registration_facility_name,
    registration_facility.facility_type AS registration_facility_type,
    registration_facility.state AS registration_facility_state,
    registration_facility.district AS registration_facility_district,
    mh.hypertension,
    mh.diabetes,
    GREATEST((0)::double precision, date_part('day'::text, (now() - (next_scheduled_appointment.scheduled_date)::timestamp with time zone))) AS days_overdue,
    next_scheduled_appointment.id AS next_scheduled_appointment_id,
    next_scheduled_appointment.scheduled_date AS next_scheduled_appointment_scheduled_date,
    next_scheduled_appointment.status AS next_scheduled_appointment_status,
    next_scheduled_appointment.remind_on AS next_scheduled_appointment_remind_on,
    next_scheduled_appointment.appointment_facility_id AS next_scheduled_appointment_facility_id,
    next_scheduled_appointment.appointment_facility_name AS next_scheduled_appointment_facility_name,
    next_scheduled_appointment.appointment_facility_type AS next_scheduled_appointment_facility_type,
    next_scheduled_appointment.appointment_district AS next_scheduled_appointment_district,
    next_scheduled_appointment.appointment_state AS next_scheduled_appointment_state,
    latest_blood_pressures.latest_blood_pressure_1_id,
    latest_blood_pressures.latest_blood_pressure_1_recorded_at,
    latest_blood_pressures.latest_blood_pressure_1_systolic,
    latest_blood_pressures.latest_blood_pressure_1_diastolic,
    latest_blood_pressures.latest_blood_pressure_1_facility_name,
    latest_blood_pressures.latest_blood_pressure_1_facility_type,
    latest_blood_pressures.latest_blood_pressure_1_district,
    latest_blood_pressures.latest_blood_pressure_1_state,
    latest_blood_pressures.latest_blood_pressure_1_follow_up_facility_name,
    latest_blood_pressures.latest_blood_pressure_1_follow_up_date,
    latest_blood_pressures.latest_blood_pressure_1_follow_up_days,
    latest_blood_pressures.latest_blood_pressure_1_medication_updated,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_1_name,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_1_dosage,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_2_name,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_2_dosage,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_3_name,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_3_dosage,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_4_name,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_4_dosage,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_5_name,
    latest_blood_pressures.latest_blood_pressure_1_prescription_drug_5_dosage,
    latest_blood_pressures.latest_blood_pressure_1_other_prescription_drugs,
    latest_blood_pressures.latest_blood_pressure_2_id,
    latest_blood_pressures.latest_blood_pressure_2_recorded_at,
    latest_blood_pressures.latest_blood_pressure_2_systolic,
    latest_blood_pressures.latest_blood_pressure_2_diastolic,
    latest_blood_pressures.latest_blood_pressure_2_facility_name,
    latest_blood_pressures.latest_blood_pressure_2_facility_type,
    latest_blood_pressures.latest_blood_pressure_2_district,
    latest_blood_pressures.latest_blood_pressure_2_state,
    latest_blood_pressures.latest_blood_pressure_2_follow_up_facility_name,
    latest_blood_pressures.latest_blood_pressure_2_follow_up_date,
    latest_blood_pressures.latest_blood_pressure_2_follow_up_days,
    latest_blood_pressures.latest_blood_pressure_2_medication_updated,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_1_name,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_1_dosage,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_2_name,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_2_dosage,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_3_name,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_3_dosage,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_4_name,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_4_dosage,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_5_name,
    latest_blood_pressures.latest_blood_pressure_2_prescription_drug_5_dosage,
    latest_blood_pressures.latest_blood_pressure_2_other_prescription_drugs,
    latest_blood_pressures.latest_blood_pressure_3_id,
    latest_blood_pressures.latest_blood_pressure_3_recorded_at,
    latest_blood_pressures.latest_blood_pressure_3_systolic,
    latest_blood_pressures.latest_blood_pressure_3_diastolic,
    latest_blood_pressures.latest_blood_pressure_3_facility_name,
    latest_blood_pressures.latest_blood_pressure_3_facility_type,
    latest_blood_pressures.latest_blood_pressure_3_district,
    latest_blood_pressures.latest_blood_pressure_3_state,
    latest_blood_pressures.latest_blood_pressure_3_follow_up_facility_name,
    latest_blood_pressures.latest_blood_pressure_3_follow_up_date,
    latest_blood_pressures.latest_blood_pressure_3_follow_up_days,
    latest_blood_pressures.latest_blood_pressure_3_medication_updated,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_1_name,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_1_dosage,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_2_name,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_2_dosage,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_3_name,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_3_dosage,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_4_name,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_4_dosage,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_5_name,
    latest_blood_pressures.latest_blood_pressure_3_prescription_drug_5_dosage,
    latest_blood_pressures.latest_blood_pressure_3_other_prescription_drugs,
    latest_blood_sugars.latest_blood_sugar_1_id,
    latest_blood_sugars.latest_blood_sugar_1_recorded_at,
    latest_blood_sugars.latest_blood_sugar_1_blood_sugar_type,
    latest_blood_sugars.latest_blood_sugar_1_blood_sugar_value,
    latest_blood_sugars.latest_blood_sugar_1_facility_name,
    latest_blood_sugars.latest_blood_sugar_1_facility_type,
    latest_blood_sugars.latest_blood_sugar_1_district,
    latest_blood_sugars.latest_blood_sugar_1_state,
    latest_blood_sugars.latest_blood_sugar_1_follow_up_facility_name,
    latest_blood_sugars.latest_blood_sugar_1_follow_up_date,
    latest_blood_sugars.latest_blood_sugar_1_follow_up_days,
    latest_blood_sugars.latest_blood_sugar_2_id,
    latest_blood_sugars.latest_blood_sugar_2_recorded_at,
    latest_blood_sugars.latest_blood_sugar_2_blood_sugar_type,
    latest_blood_sugars.latest_blood_sugar_2_blood_sugar_value,
    latest_blood_sugars.latest_blood_sugar_2_facility_name,
    latest_blood_sugars.latest_blood_sugar_2_facility_type,
    latest_blood_sugars.latest_blood_sugar_2_district,
    latest_blood_sugars.latest_blood_sugar_2_state,
    latest_blood_sugars.latest_blood_sugar_2_follow_up_facility_name,
    latest_blood_sugars.latest_blood_sugar_2_follow_up_date,
    latest_blood_sugars.latest_blood_sugar_2_follow_up_days,
    latest_blood_sugars.latest_blood_sugar_3_id,
    latest_blood_sugars.latest_blood_sugar_3_recorded_at,
    latest_blood_sugars.latest_blood_sugar_3_blood_sugar_type,
    latest_blood_sugars.latest_blood_sugar_3_blood_sugar_value,
    latest_blood_sugars.latest_blood_sugar_3_facility_name,
    latest_blood_sugars.latest_blood_sugar_3_facility_type,
    latest_blood_sugars.latest_blood_sugar_3_district,
    latest_blood_sugars.latest_blood_sugar_3_state,
    latest_blood_sugars.latest_blood_sugar_3_follow_up_facility_name,
    latest_blood_sugars.latest_blood_sugar_3_follow_up_date,
    latest_blood_sugars.latest_blood_sugar_3_follow_up_days
   FROM (((((((((patients p
     LEFT JOIN latest_bp_passport ON ((latest_bp_passport.patient_id = p.id)))
     LEFT JOIN latest_phone_number ON ((latest_phone_number.patient_id = p.id)))
     LEFT JOIN addresses ON ((addresses.id = p.address_id)))
     LEFT JOIN facilities assigned_facility ON ((assigned_facility.id = p.assigned_facility_id)))
     LEFT JOIN facilities registration_facility ON ((registration_facility.id = p.registration_facility_id)))
     LEFT JOIN latest_medical_history mh ON ((mh.patient_id = p.id)))
     LEFT JOIN latest_blood_pressures ON ((latest_blood_pressures.patient_id = p.id)))
     LEFT JOIN latest_blood_sugars ON ((latest_blood_sugars.patient_id = p.id)))
     LEFT JOIN next_scheduled_appointment ON ((next_scheduled_appointment.patient_id = p.id)))
  WHERE (p.deleted_at IS NULL);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views