reporting_patient_visits

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

patient_id

uuid

2147483647

√

null

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

assigned_facility_id

uuid

2147483647

√

null

registration_facility_id

uuid

2147483647

√

null

patient_recorded_at

timestamp

29,6

√

null

encounter_id

uuid

2147483647

√

null

encounter_facility_id

uuid

2147483647

√

null

encounter_recorded_at

timestamp

29,6

√

null

prescription_drug_id

uuid

2147483647

√

null

prescription_drug_facility_id

uuid

2147483647

√

null

prescription_drug_recorded_at

timestamp

29,6

√

null

appointment_id

uuid

2147483647

√

null

appointment_creation_facility_id

uuid

2147483647

√

null

appointment_recorded_at

timestamp

29,6

√

null

visited_facility_ids

_uuid

2147483647

√

null

visited_at

timestamp

29,6

√

null

months_since_registration

float8

17,17

√

null

quarters_since_registration

float8

17,17

√

null

months_since_visit

float8

17,17

√

null

quarters_since_visit

float8

17,17

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT ON (p.id, p.month_date) p.id AS patient_id,
    p.month_date,
    p.month,
    p.quarter,
    p.year,
    p.month_string,
    p.quarter_string,
    p.assigned_facility_id,
    p.registration_facility_id,
    timezone('UTC'::text, timezone('UTC'::text, p.recorded_at)) AS patient_recorded_at,
    e.id AS encounter_id,
    e.facility_id AS encounter_facility_id,
    timezone('UTC'::text, timezone('UTC'::text, e.recorded_at)) AS encounter_recorded_at,
    pd.id AS prescription_drug_id,
    pd.facility_id AS prescription_drug_facility_id,
    timezone('UTC'::text, timezone('UTC'::text, pd.recorded_at)) AS prescription_drug_recorded_at,
    app.id AS appointment_id,
    app.creation_facility_id AS appointment_creation_facility_id,
    timezone('UTC'::text, timezone('UTC'::text, app.recorded_at)) AS appointment_recorded_at,
    array_remove(ARRAY[
        CASE
            WHEN (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, e.recorded_at)), 'YYYY-MM'::text) = p.month_string) THEN e.facility_id
            ELSE NULL::uuid
        END,
        CASE
            WHEN (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, pd.recorded_at)), 'YYYY-MM'::text) = p.month_string) THEN pd.facility_id
            ELSE NULL::uuid
        END,
        CASE
            WHEN (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, app.recorded_at)), 'YYYY-MM'::text) = p.month_string) THEN app.creation_facility_id
            ELSE NULL::uuid
        END], NULL::uuid) AS visited_facility_ids,
    timezone('UTC'::text, timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at))) AS visited_at,
    (((p.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, p.recorded_at)))) * (12)::double precision) + (p.month - date_part('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, p.recorded_at))))) AS months_since_registration,
    (((p.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, p.recorded_at)))) * (4)::double precision) + (p.quarter - date_part('quarter'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, p.recorded_at))))) AS quarters_since_registration,
    (((p.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at))))) * (12)::double precision) + (p.month - date_part('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at)))))) AS months_since_visit,
    (((p.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at))))) * (4)::double precision) + (p.quarter - date_part('quarter'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at)))))) AS quarters_since_visit
   FROM (((( SELECT p_1.id,
            p_1.full_name,
            p_1.age,
            p_1.gender,
            p_1.date_of_birth,
            p_1.status,
            p_1.created_at,
            p_1.updated_at,
            p_1.address_id,
            p_1.age_updated_at,
            p_1.device_created_at,
            p_1.device_updated_at,
            p_1.test_data,
            p_1.registration_facility_id,
            p_1.registration_user_id,
            p_1.deleted_at,
            p_1.contacted_by_counsellor,
            p_1.could_not_contact_reason,
            p_1.recorded_at,
            p_1.reminder_consent,
            p_1.deleted_by_user_id,
            p_1.deleted_reason,
            p_1.assigned_facility_id,
            cal.month_date,
            cal.month,
            cal.quarter,
            cal.year,
            cal.month_string,
            cal.quarter_string
           FROM (patients p_1
             LEFT JOIN reporting_months cal ON ((to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('utc'::text, p_1.recorded_at)), 'YYYY-MM'::text) <= cal.month_string)))) p
     LEFT JOIN LATERAL ( SELECT timezone('UTC'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), (encounters.encountered_on)::timestamp without time zone)) AS recorded_at,
            encounters.id,
            encounters.facility_id,
            encounters.patient_id,
            encounters.encountered_on,
            encounters.timezone_offset,
            encounters.notes,
            encounters.metadata,
            encounters.device_created_at,
            encounters.device_updated_at,
            encounters.deleted_at,
            encounters.created_at,
            encounters.updated_at
           FROM encounters
          WHERE ((encounters.patient_id = p.id) AND (to_char((encounters.encountered_on)::timestamp with time zone, 'YYYY-MM'::text) <= p.month_string) AND (encounters.deleted_at IS NULL))
          ORDER BY encounters.encountered_on DESC
         LIMIT 1) e ON (true))
     LEFT JOIN LATERAL ( SELECT prescription_drugs.device_created_at AS recorded_at,
            prescription_drugs.id,
            prescription_drugs.name,
            prescription_drugs.rxnorm_code,
            prescription_drugs.dosage,
            prescription_drugs.device_created_at,
            prescription_drugs.device_updated_at,
            prescription_drugs.created_at,
            prescription_drugs.updated_at,
            prescription_drugs.patient_id,
            prescription_drugs.facility_id,
            prescription_drugs.is_protocol_drug,
            prescription_drugs.is_deleted,
            prescription_drugs.deleted_at,
            prescription_drugs.user_id,
            prescription_drugs.frequency,
            prescription_drugs.duration_in_days,
            prescription_drugs.teleconsultation_id
           FROM prescription_drugs
          WHERE ((prescription_drugs.patient_id = p.id) AND (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, prescription_drugs.device_created_at)), 'YYYY-MM'::text) <= p.month_string) AND (prescription_drugs.deleted_at IS NULL))
          ORDER BY prescription_drugs.device_created_at DESC
         LIMIT 1) pd ON (true))
     LEFT JOIN LATERAL ( SELECT appointments.device_created_at AS recorded_at,
            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 appointments
          WHERE ((appointments.patient_id = p.id) AND (to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, appointments.device_created_at)), 'YYYY-MM'::text) <= p.month_string) AND (appointments.deleted_at IS NULL))
          ORDER BY appointments.device_created_at DESC
         LIMIT 1) app ON (true))
  WHERE (p.deleted_at IS NULL)
  ORDER BY p.id, p.month_date, (timezone('UTC'::text, timezone('UTC'::text, GREATEST(e.recorded_at, pd.recorded_at, app.recorded_at)))) DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views