reporting_patient_states

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

patient_id

uuid

2147483647

√

null

ID of the patient

recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the patient was registered

status

varchar

2147483647

√

null

active, dead, migrated, etc

gender

varchar

2147483647

√

null

Gender of the patient

age

int4

√

null

Age of the patient as entered by a nurse

age_updated_at

timestamp

29,6

√

null

Time (in UTC) at which the field ‘age’ was last updated

date_of_birth

date

√

null

Date of birth of the patient

current_age

float8

17,17

√

null

Patient’s age as of today, based on ‘age’, ‘age_updated_at’ and ‘date_of_birth’. This will have the same value for a patient across all rows.

month_date

date

√

null

The reporting month for this row, represented as the date at the beginning of the month

month

float8

17,17

√

null

Month (1-12) of year

quarter

float8

17,17

√

null

Quarter (1-4) of year

year

float8

17,17

√

null

Year in YYYY format

month_string

text

2147483647

√

null

String that represents a month, in YYYY-MM format

quarter_string

text

2147483647

√

null

String that represents a quarter, in YYYY-Q format

hypertension

text

2147483647

√

null

Has the patient been diagnosed with hypertension? Values can be yes, no, unknown, or null if the data is unavailable.

prior_heart_attack

text

2147483647

√

null

Has the patient had a heart attack? Values can be yes, no, unknown, or null if the data is unavailable.

prior_stroke

text

2147483647

√

null

Has the patient has had a stroke? Values can be yes, no, unknown, or null if the data is unavailable.

chronic_kidney_disease

text

2147483647

√

null

Has the patient had a chronic kidney disease? Values can be yes, no, unknown, or null if the data is unavailable.

receiving_treatment_for_hypertension

text

2147483647

√

null

Was the patient already receiving treatment for hypertension during registration? Values can be yes, no, unknown, or null if the data is unavailable.

diabetes

text

2147483647

√

null

Has the patient been diagnosed with diabetes? Values can be yes, no, unknown, or null if the data is unavailable.

assigned_facility_id

uuid

2147483647

√

null

ID of the patient’s assigned facility

assigned_facility_size

varchar

2147483647

√

null

Size of the patient’s assigned facility

assigned_facility_type

varchar

2147483647

√

null

Type of the patient’s assigned facility

assigned_facility_slug

varchar

2147483647

√

null

Human readable ID of the patient’s assigned facility

assigned_facility_region_id

uuid

2147483647

√

null

Region ID of the patient’s assigned facility

assigned_block_slug

varchar

2147483647

√

null

Human readable ID of the patient’s assigned facility’s block

assigned_block_region_id

uuid

2147483647

√

null

ID of the patient’s assigned facility’s block

assigned_district_slug

varchar

2147483647

√

null

Human readable ID of the patient’s assigned facility’s district

assigned_district_region_id

uuid

2147483647

√

null

ID of the patient’s assigned facility’s district

assigned_state_slug

varchar

2147483647

√

null

Human readable ID of the patient’s assigned facility’s state

assigned_state_region_id

uuid

2147483647

√

null

ID of the patient’s assigned facility’s state

assigned_organization_slug

varchar

2147483647

√

null

Human readable ID of the patient’s assigned facility’s organization

assigned_organization_region_id

uuid

2147483647

√

null

ID of the patient’s assigned facility’s organization

registration_facility_id

uuid

2147483647

√

null

ID of the patient’s registration facility

registration_facility_size

varchar

2147483647

√

null

Size of the patient’s registration facility

registration_facility_type

varchar

2147483647

√

null

Type of the patient’s registration facility

registration_facility_slug

varchar

2147483647

√

null

Human readable ID of the patient’s registration facility

registration_facility_region_id

uuid

2147483647

√

null

Region ID of the patient’s registration facility

registration_block_slug

varchar

2147483647

√

null

Human readable ID of the patient’s registration facility’s block

registration_block_region_id

uuid

2147483647

√

null

ID of the patient’s registration facility’s block

registration_district_slug

varchar

2147483647

√

null

Human readable ID of the patient’s registration facility’s district

registration_district_region_id

uuid

2147483647

√

null

ID of the patient’s registration facility’s district

registration_state_slug

varchar

2147483647

√

null

Human readable ID of the patient’s registration facility’s state

registration_state_region_id

uuid

2147483647

√

null

ID of the patient’s registration facility’s state

registration_organization_slug

varchar

2147483647

√

null

Human readable ID of the patient’s registration facility’s organization

registration_organization_region_id

uuid

2147483647

√

null

ID of the patient’s registration facility’s organization

blood_pressure_id

uuid

2147483647

√

null

ID of the latest BP as of this month. Use this to join with the blood_pressures table.

bp_facility_id

uuid

2147483647

√

null

ID of the facility at which the latest BP was recorded as of this month

bp_recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the latest BP as of this month was recorded

systolic

int4

√

null

Systolic of the latest BP as of this month

diastolic

int4

√

null

Diastolic of the latest BP as of this month

blood_sugar_id

uuid

2147483647

√

null

ID of the latest blood sugar as of this month. Use this to join with the blood_sugars table.

bs_facility_id

uuid

2147483647

√

null

ID of the facility at which the latest blood sugar was recorded as of this month

bs_recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the latest blood sugar as of this month was recorded

blood_sugar_type

varchar

2147483647

√

null

Blood sugar type of the latest measure as of this month

blood_sugar_value

numeric

√

null

Blood sugar value of the latest measure as of this month

blood_sugar_risk_state

text

2147483647

√

null

Blood sugar risk state of the latest measure as of this month

encounter_id

uuid

2147483647

√

null

ID of the latest encounter as of this month. Use this to join with the encounters table.

encounter_recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the latest encounter as of this month was recorded

prescription_drug_id

uuid

2147483647

√

null

ID of the latest prescription drug as of this month. Use this to join with the prescription drugs table.

prescription_drug_recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the latest prescription drug as of this month was recorded

appointment_id

uuid

2147483647

√

null

ID of the latest appointment as of this month. Use this to join with the appointments table.

appointment_recorded_at

timestamp

29,6

√

null

Time (in UTC) at which the latest appointment as of this month was recorded

visited_facility_ids

_uuid

2147483647

√

null

IDs of the facilities visited this month

months_since_registration

float8

17,17

√

null

Number of months since registration. If a patient was registered on 31st Jan, it would be 1 month since registration on 1st Feb.

quarters_since_registration

float8

17,17

√

null

Number of quarters since registration. If a patient was registered on 31st Dec, it would be 1 quarter since registration on 1st Jan.

months_since_visit

float8

17,17

√

null

Number of months since the patient’s last visit. If a patient visited on 31st Jan, it would be 1 month since the visit on 1st Feb.

quarters_since_visit

float8

17,17

√

null

Number of quarters since the patient’s last visit. If a patient visited on 31st Jan, it would be 1 quarter since the visit on 1st Jan.

months_since_bp

float8

17,17

√

null

Number of months since the patient’s last BP recording. If a patient had a BP reading on 31st Jan, it would be 1 month since BP on 1st Feb.

quarters_since_bp

float8

17,17

√

null

Number of quarters since the patient’s last BP recording. If a patient had a BP reading on 31st Jan, it would be 1 quarter since BP on 1st Jan.

months_since_bs

float8

17,17

√

null

Number of months since the patient’s last blood sugar recording. If a patient had a blood sugar reading on 31st Jan, it would be 1 month since blood sugar on 1st Feb.

quarters_since_bs

float8

17,17

√

null

Number of quarters since the patient’s last blood sugar recording. If a patient had a blood sugar reading on 31st Jan, it would be 1 quarter since blood sugar on 1st Jan.

last_bp_state

text

2147483647

√

null

The state of the last BP recorded: controlled, uncontrolled, or unknown

htn_care_state

text

2147483647

√

null

Is the patient under_care, lost_to_follow_up, or dead as of this month?

htn_treatment_outcome_in_last_3_months

text

2147483647

√

null

For the visiting period of the last 3 months, is this patient’s treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?

htn_treatment_outcome_in_last_2_months

text

2147483647

√

null

For the visiting period of the last 2 months, is this patient’s treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?

htn_treatment_outcome_in_quarter

text

2147483647

√

null

For the visiting period of the current quarter, is this patient’s treatment outcome controlled, uncontrolled, missed_visit, or visited_no_bp?

diabetes_treatment_outcome_in_last_3_months

text

2147483647

√

null

For the visiting period of the last 3 months, is this patient’s diabetes treatment outcome bs_under_200, bs_200_to_300, bs_over_300, missed_visit, or visited_no_bp?

diabetes_treatment_outcome_in_last_2_months

text

2147483647

√

null

For the visiting period of the last 2 months, is this patient’s diabetes treatment outcome bs_under_200, bs_200_to_300, bs_over_300, missed_visit, or visited_no_bp?

diabetes_treatment_outcome_in_quarter

text

2147483647

√

null

For the visiting period of the current quarter, is this patient’s diabetes treatment outcome bs_under_200, bs_200_to_300, bs_over_300, missed_visit, or visited_no_bp?

titrated

bool

√

null

True, if the patient had an increase in dosage of any hypertension drug in a visit this month.

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT ON (p.id, cal.month_date) p.id AS patient_id,
    timezone('UTC'::text, timezone('UTC'::text, p.recorded_at)) AS recorded_at,
    p.status,
    p.gender,
    p.age,
    timezone('UTC'::text, timezone('UTC'::text, p.age_updated_at)) AS age_updated_at,
    p.date_of_birth,
    date_part('year'::text, COALESCE(age((p.date_of_birth)::timestamp with time zone), (make_interval(years => p.age) + age(p.age_updated_at)))) AS current_age,
    cal.month_date,
    cal.month,
    cal.quarter,
    cal.year,
    cal.month_string,
    cal.quarter_string,
    mh.hypertension,
    mh.prior_heart_attack,
    mh.prior_stroke,
    mh.chronic_kidney_disease,
    mh.receiving_treatment_for_hypertension,
    mh.diabetes,
    p.assigned_facility_id,
    assigned_facility.facility_size AS assigned_facility_size,
    assigned_facility.facility_type AS assigned_facility_type,
    assigned_facility.facility_region_slug AS assigned_facility_slug,
    assigned_facility.facility_region_id AS assigned_facility_region_id,
    assigned_facility.block_slug AS assigned_block_slug,
    assigned_facility.block_region_id AS assigned_block_region_id,
    assigned_facility.district_slug AS assigned_district_slug,
    assigned_facility.district_region_id AS assigned_district_region_id,
    assigned_facility.state_slug AS assigned_state_slug,
    assigned_facility.state_region_id AS assigned_state_region_id,
    assigned_facility.organization_slug AS assigned_organization_slug,
    assigned_facility.organization_region_id AS assigned_organization_region_id,
    p.registration_facility_id,
    registration_facility.facility_size AS registration_facility_size,
    registration_facility.facility_type AS registration_facility_type,
    registration_facility.facility_region_slug AS registration_facility_slug,
    registration_facility.facility_region_id AS registration_facility_region_id,
    registration_facility.block_slug AS registration_block_slug,
    registration_facility.block_region_id AS registration_block_region_id,
    registration_facility.district_slug AS registration_district_slug,
    registration_facility.district_region_id AS registration_district_region_id,
    registration_facility.state_slug AS registration_state_slug,
    registration_facility.state_region_id AS registration_state_region_id,
    registration_facility.organization_slug AS registration_organization_slug,
    registration_facility.organization_region_id AS registration_organization_region_id,
    bps.blood_pressure_id,
    bps.blood_pressure_facility_id AS bp_facility_id,
    bps.blood_pressure_recorded_at AS bp_recorded_at,
    bps.systolic,
    bps.diastolic,
    bss.blood_sugar_id,
    bss.blood_sugar_facility_id AS bs_facility_id,
    bss.blood_sugar_recorded_at AS bs_recorded_at,
    bss.blood_sugar_type,
    bss.blood_sugar_value,
    bss.blood_sugar_risk_state,
    visits.encounter_id,
    visits.encounter_recorded_at,
    visits.prescription_drug_id,
    visits.prescription_drug_recorded_at,
    visits.appointment_id,
    visits.appointment_recorded_at,
    visits.visited_facility_ids,
    (((cal.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at)))) * (12)::double precision) + (cal.month - date_part('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at))))) AS months_since_registration,
    (((cal.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at)))) * (4)::double precision) + (cal.quarter - date_part('quarter'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at))))) AS quarters_since_registration,
    visits.months_since_visit,
    visits.quarters_since_visit,
    bps.months_since_bp,
    bps.quarters_since_bp,
    bss.months_since_bs,
    bss.quarters_since_bs,
        CASE
            WHEN ((bps.systolic IS NULL) OR (bps.diastolic IS NULL)) THEN 'unknown'::text
            WHEN ((bps.systolic < 140) AND (bps.diastolic < 90)) THEN 'controlled'::text
            ELSE 'uncontrolled'::text
        END AS last_bp_state,
        CASE
            WHEN ((p.status)::text = 'dead'::text) THEN 'dead'::text
            WHEN (((((cal.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at)))) * (12)::double precision) + (cal.month - date_part('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at))))) < (12)::double precision) OR (visits.months_since_visit < (12)::double precision)) THEN 'under_care'::text
            ELSE 'lost_to_follow_up'::text
        END AS htn_care_state,
        CASE
            WHEN ((visits.months_since_visit >= (3)::double precision) OR (visits.months_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bps.months_since_bp >= (3)::double precision) OR (bps.months_since_bp IS NULL)) THEN 'visited_no_bp'::text
            WHEN ((bps.systolic < 140) AND (bps.diastolic < 90)) THEN 'controlled'::text
            ELSE 'uncontrolled'::text
        END AS htn_treatment_outcome_in_last_3_months,
        CASE
            WHEN ((visits.months_since_visit >= (2)::double precision) OR (visits.months_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bps.months_since_bp >= (2)::double precision) OR (bps.months_since_bp IS NULL)) THEN 'visited_no_bp'::text
            WHEN ((bps.systolic < 140) AND (bps.diastolic < 90)) THEN 'controlled'::text
            ELSE 'uncontrolled'::text
        END AS htn_treatment_outcome_in_last_2_months,
        CASE
            WHEN ((visits.quarters_since_visit >= (1)::double precision) OR (visits.quarters_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bps.quarters_since_bp >= (1)::double precision) OR (bps.quarters_since_bp IS NULL)) THEN 'visited_no_bp'::text
            WHEN ((bps.systolic < 140) AND (bps.diastolic < 90)) THEN 'controlled'::text
            ELSE 'uncontrolled'::text
        END AS htn_treatment_outcome_in_quarter,
        CASE
            WHEN ((visits.months_since_visit >= (3)::double precision) OR (visits.months_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bss.months_since_bs >= (3)::double precision) OR (bss.months_since_bs IS NULL)) THEN 'visited_no_bs'::text
            ELSE bss.blood_sugar_risk_state
        END AS diabetes_treatment_outcome_in_last_3_months,
        CASE
            WHEN ((visits.months_since_visit >= (2)::double precision) OR (visits.months_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bss.months_since_bs >= (2)::double precision) OR (bss.months_since_bs IS NULL)) THEN 'visited_no_bs'::text
            ELSE bss.blood_sugar_risk_state
        END AS diabetes_treatment_outcome_in_last_2_months,
        CASE
            WHEN ((visits.quarters_since_visit >= (1)::double precision) OR (visits.quarters_since_visit IS NULL)) THEN 'missed_visit'::text
            WHEN ((bss.quarters_since_bs >= (1)::double precision) OR (bss.quarters_since_bs IS NULL)) THEN 'visited_no_bs'::text
            ELSE bss.blood_sugar_risk_state
        END AS diabetes_treatment_outcome_in_quarter,
    ((current_meds.amlodipine > past_meds.amlodipine) OR (current_meds.telmisartan > past_meds.telmisartan) OR (current_meds.losartan > past_meds.losartan) OR (current_meds.atenolol > past_meds.atenolol) OR (current_meds.enalapril > past_meds.enalapril) OR (current_meds.chlorthalidone > past_meds.chlorthalidone) OR (current_meds.hydrochlorothiazide > past_meds.hydrochlorothiazide)) AS titrated
   FROM (((((((((patients p
     LEFT JOIN reporting_months cal ON ((to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at)), 'YYYY-MM'::text) <= to_char((cal.month_date)::timestamp with time zone, 'YYYY-MM'::text))))
     LEFT JOIN reporting_patient_blood_pressures bps ON (((p.id = bps.patient_id) AND (cal.month = bps.month) AND (cal.year = bps.year))))
     LEFT JOIN reporting_patient_blood_sugars bss ON (((p.id = bss.patient_id) AND (cal.month = bss.month) AND (cal.year = bss.year))))
     LEFT JOIN reporting_patient_visits visits ON (((p.id = visits.patient_id) AND (cal.month = visits.month) AND (cal.year = visits.year))))
     LEFT JOIN medical_histories mh ON (((p.id = mh.patient_id) AND (mh.deleted_at IS NULL))))
     LEFT JOIN reporting_prescriptions current_meds ON (((current_meds.patient_id = p.id) AND (cal.month_date = current_meds.month_date))))
     LEFT JOIN reporting_prescriptions past_meds ON (((past_meds.patient_id = p.id) AND (cal.month_date = (past_meds.month_date + '1 mon'::interval)))))
     JOIN reporting_facilities registration_facility ON ((registration_facility.facility_id = p.registration_facility_id)))
     JOIN reporting_facilities assigned_facility ON ((assigned_facility.facility_id = p.assigned_facility_id)))
  WHERE (p.deleted_at IS NULL)
  ORDER BY p.id, cal.month_date;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views