reporting_quarterly_facility_states

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

month_date

date

√

null

month

float8

17,17

√

null

quarter

float8

17,17

√

null

year

float8

17,17

√

null

month_string

text

2147483647

√

null

quarter_string

text

2147483647

√

null

facility_id

uuid

2147483647

√

null

facility_name

varchar

2147483647

√

null

facility_type

varchar

2147483647

√

null

facility_size

varchar

2147483647

√

null

facility_region_id

uuid

2147483647

√

null

facility_region_name

varchar

2147483647

√

null

facility_region_slug

varchar

2147483647

√

null

block_region_id

uuid

2147483647

√

null

block_name

varchar

2147483647

√

null

block_slug

varchar

2147483647

√

null

district_id

uuid

2147483647

√

null

district_region_id

uuid

2147483647

√

null

district_name

varchar

2147483647

√

null

district_slug

varchar

2147483647

√

null

state_region_id

uuid

2147483647

√

null

state_name

varchar

2147483647

√

null

state_slug

varchar

2147483647

√

null

organization_id

uuid

2147483647

√

null

organization_region_id

uuid

2147483647

√

null

organization_name

varchar

2147483647

√

null

organization_slug

varchar

2147483647

√

null

quarterly_cohort_controlled

int8

√

null

quarterly_cohort_uncontrolled

int8

√

null

quarterly_cohort_missed_visit

int8

√

null

quarterly_cohort_visited_no_bp

int8

√

null

quarterly_cohort_patients

int8

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH quarterly_cohort_outcomes AS (
         SELECT reporting_patient_states.assigned_facility_region_id AS region_id,
            reporting_patient_states.quarter_string,
            count(DISTINCT reporting_patient_states.patient_id) FILTER (WHERE (reporting_patient_states.htn_treatment_outcome_in_quarter = 'visited_no_bp'::text)) AS visited_no_bp,
            count(DISTINCT reporting_patient_states.patient_id) FILTER (WHERE (reporting_patient_states.htn_treatment_outcome_in_quarter = 'controlled'::text)) AS controlled,
            count(DISTINCT reporting_patient_states.patient_id) FILTER (WHERE (reporting_patient_states.htn_treatment_outcome_in_quarter = 'uncontrolled'::text)) AS uncontrolled,
            count(DISTINCT reporting_patient_states.patient_id) FILTER (WHERE (reporting_patient_states.htn_treatment_outcome_in_quarter = 'missed_visit'::text)) AS missed_visit,
            count(DISTINCT reporting_patient_states.patient_id) AS patients
           FROM reporting_patient_states
          WHERE ((reporting_patient_states.hypertension = 'yes'::text) AND ((((reporting_patient_states.month)::integer % 3) = 0) OR (reporting_patient_states.month_string = to_char(now(), 'YYYY-MM'::text))) AND (reporting_patient_states.quarters_since_registration = (1)::double precision))
          GROUP BY reporting_patient_states.assigned_facility_region_id, reporting_patient_states.quarter_string
        )
 SELECT cal.month_date,
    cal.month,
    cal.quarter,
    cal.year,
    cal.month_string,
    cal.quarter_string,
    rf.facility_id,
    rf.facility_name,
    rf.facility_type,
    rf.facility_size,
    rf.facility_region_id,
    rf.facility_region_name,
    rf.facility_region_slug,
    rf.block_region_id,
    rf.block_name,
    rf.block_slug,
    rf.district_id,
    rf.district_region_id,
    rf.district_name,
    rf.district_slug,
    rf.state_region_id,
    rf.state_name,
    rf.state_slug,
    rf.organization_id,
    rf.organization_region_id,
    rf.organization_name,
    rf.organization_slug,
    quarterly_cohort_outcomes.controlled AS quarterly_cohort_controlled,
    quarterly_cohort_outcomes.uncontrolled AS quarterly_cohort_uncontrolled,
    quarterly_cohort_outcomes.missed_visit AS quarterly_cohort_missed_visit,
    quarterly_cohort_outcomes.visited_no_bp AS quarterly_cohort_visited_no_bp,
    quarterly_cohort_outcomes.patients AS quarterly_cohort_patients
   FROM ((reporting_facilities rf
     JOIN reporting_months cal ON (((((cal.month)::integer % 3) = 0) OR (cal.month_string = to_char(now(), 'YYYY-MM'::text)))))
     LEFT JOIN quarterly_cohort_outcomes ON (((quarterly_cohort_outcomes.quarter_string = cal.quarter_string) AND (quarterly_cohort_outcomes.region_id = rf.facility_region_id))));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views