reporting_facility_appointment_scheduled_days

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

facility_id

uuid

2147483647

√

null

month_date

date

√

null

htn_appts_scheduled_0_to_14_days

int4

√

null

htn_appts_scheduled_15_to_31_days

int4

√

null

htn_appts_scheduled_32_to_62_days

int4

√

null

htn_appts_scheduled_more_than_62_days

int4

√

null

htn_total_appts_scheduled

int4

√

null

diabetes_appts_scheduled_0_to_14_days

int4

√

null

diabetes_appts_scheduled_15_to_31_days

int4

√

null

diabetes_appts_scheduled_32_to_62_days

int4

√

null

diabetes_appts_scheduled_more_than_62_days

int4

√

null

diabetes_total_appts_scheduled

int4

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH latest_appointments_per_patient_per_month AS (
         SELECT DISTINCT ON (a.patient_id, (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, a.device_created_at)), 'YYYY-MM-01'::text))::date) a.id,
            a.patient_id,
            a.facility_id,
            a.scheduled_date,
            a.status,
            a.cancel_reason,
            a.device_created_at,
            a.device_updated_at,
            a.created_at,
            a.updated_at,
            a.remind_on,
            a.agreed_to_visit,
            a.deleted_at,
            a.appointment_type,
            a.user_id,
            a.creation_facility_id,
            mh.hypertension,
            mh.diabetes,
            (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, a.device_created_at)), 'YYYY-MM-01'::text))::date AS month_date
           FROM ((appointments a
             JOIN patients p ON ((p.id = a.patient_id)))
             JOIN medical_histories mh ON ((mh.patient_id = a.patient_id)))
          WHERE ((a.scheduled_date >= date_trunc('day'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, a.device_created_at)))) AND (a.device_created_at >= date_trunc('month'::text, (timezone('UTC'::text, now()) - '6 mons'::interval))) AND (date_trunc('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, a.device_created_at))) > date_trunc('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, p.recorded_at)))) AND (p.deleted_at IS NULL) AND (a.deleted_at IS NULL) AND ((mh.hypertension = 'yes'::text) OR (mh.diabetes = 'yes'::text)))
          ORDER BY a.patient_id, (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, a.device_created_at)), 'YYYY-MM-01'::text))::date, a.device_created_at DESC
        ), scheduled_days_distribution AS (
         SELECT latest_appointments_per_patient_per_month.month_date,
            width_bucket((date_part('days'::text, ((latest_appointments_per_patient_per_month.scheduled_date)::timestamp without time zone - date_trunc('day'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, latest_appointments_per_patient_per_month.device_created_at))))))::integer, ARRAY[0, 15, 32, 63]) AS bucket,
            count(*) AS number_of_appointments,
            latest_appointments_per_patient_per_month.hypertension,
            latest_appointments_per_patient_per_month.diabetes,
            latest_appointments_per_patient_per_month.creation_facility_id AS facility_id
           FROM latest_appointments_per_patient_per_month
          GROUP BY (width_bucket((date_part('days'::text, ((latest_appointments_per_patient_per_month.scheduled_date)::timestamp without time zone - date_trunc('day'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, latest_appointments_per_patient_per_month.device_created_at))))))::integer, ARRAY[0, 15, 32, 63])), latest_appointments_per_patient_per_month.creation_facility_id, latest_appointments_per_patient_per_month.month_date, latest_appointments_per_patient_per_month.hypertension, latest_appointments_per_patient_per_month.diabetes
        )
 SELECT scheduled_days_distribution.facility_id,
    scheduled_days_distribution.month_date,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 1) AND (scheduled_days_distribution.hypertension = 'yes'::text))))::integer AS htn_appts_scheduled_0_to_14_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 2) AND (scheduled_days_distribution.hypertension = 'yes'::text))))::integer AS htn_appts_scheduled_15_to_31_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 3) AND (scheduled_days_distribution.hypertension = 'yes'::text))))::integer AS htn_appts_scheduled_32_to_62_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 4) AND (scheduled_days_distribution.hypertension = 'yes'::text))))::integer AS htn_appts_scheduled_more_than_62_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE (scheduled_days_distribution.hypertension = 'yes'::text)))::integer AS htn_total_appts_scheduled,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 1) AND (scheduled_days_distribution.diabetes = 'yes'::text))))::integer AS diabetes_appts_scheduled_0_to_14_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 2) AND (scheduled_days_distribution.diabetes = 'yes'::text))))::integer AS diabetes_appts_scheduled_15_to_31_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 3) AND (scheduled_days_distribution.diabetes = 'yes'::text))))::integer AS diabetes_appts_scheduled_32_to_62_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE ((scheduled_days_distribution.bucket = 4) AND (scheduled_days_distribution.diabetes = 'yes'::text))))::integer AS diabetes_appts_scheduled_more_than_62_days,
    (sum(scheduled_days_distribution.number_of_appointments) FILTER (WHERE (scheduled_days_distribution.diabetes = 'yes'::text)))::integer AS diabetes_total_appts_scheduled
   FROM scheduled_days_distribution
  GROUP BY scheduled_days_distribution.facility_id, scheduled_days_distribution.month_date;

reporting_facility_appointment_scheduled_days

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views