reporting_patient_blood_sugars

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

blood_sugar_recorded_at

timestamp

29,6

√

null

blood_sugar_id

uuid

2147483647

√

null

patient_id

uuid

2147483647

√

null

blood_sugar_type

varchar

2147483647

√

null

blood_sugar_value

numeric

√

null

blood_sugar_facility_id

uuid

2147483647

√

null

blood_sugar_risk_state

text

2147483647

√

null

patient_registered_at

timestamp

29,6

√

null

patient_assigned_facility_id

uuid

2147483647

√

null

patient_registration_facility_id

uuid

2147483647

√

null

months_since_registration

float8

17,17

√

null

quarters_since_registration

float8

17,17

√

null

months_since_bs

float8

17,17

√

null

quarters_since_bs

float8

17,17

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT ON (bs.patient_id, cal.month_date) cal.month_date,
    cal.month,
    cal.quarter,
    cal.year,
    cal.month_string,
    cal.quarter_string,
    timezone('UTC'::text, timezone('UTC'::text, bs.recorded_at)) AS blood_sugar_recorded_at,
    bs.id AS blood_sugar_id,
    bs.patient_id,
    bs.blood_sugar_type,
    bs.blood_sugar_value,
    bs.facility_id AS blood_sugar_facility_id,
        CASE
            WHEN (((bs.blood_sugar_type)::text = 'random'::text) OR ((bs.blood_sugar_type)::text = 'post_prandial'::text)) THEN
            CASE
                WHEN (bs.blood_sugar_value < 200.0) THEN 'bs_below_200'::text
                WHEN ((bs.blood_sugar_value >= 200.0) AND (bs.blood_sugar_value < 300.0)) THEN 'bs_200_to_300'::text
                WHEN (bs.blood_sugar_value >= 300.0) THEN 'bs_over_300'::text
                ELSE NULL::text
            END
            WHEN ((bs.blood_sugar_type)::text = 'fasting'::text) THEN
            CASE
                WHEN (bs.blood_sugar_value < 126.0) THEN 'bs_below_200'::text
                WHEN ((bs.blood_sugar_value >= 126.0) AND (bs.blood_sugar_value < 200.0)) THEN 'bs_200_to_300'::text
                WHEN (bs.blood_sugar_value >= 200.0) THEN 'bs_over_300'::text
                ELSE NULL::text
            END
            WHEN ((bs.blood_sugar_type)::text = 'hba1c'::text) THEN
            CASE
                WHEN (bs.blood_sugar_value < 7.0) THEN 'bs_below_200'::text
                WHEN ((bs.blood_sugar_value >= 7.0) AND (bs.blood_sugar_value < 9.0)) THEN 'bs_200_to_300'::text
                WHEN (bs.blood_sugar_value >= 9.0) THEN 'bs_over_300'::text
                ELSE NULL::text
            END
            ELSE NULL::text
        END AS blood_sugar_risk_state,
    timezone('UTC'::text, timezone('UTC'::text, p.recorded_at)) AS patient_registered_at,
    p.assigned_facility_id AS patient_assigned_facility_id,
    p.registration_facility_id AS patient_registration_facility_id,
    (((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,
    (((cal.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, bs.recorded_at)))) * (12)::double precision) + (cal.month - date_part('month'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, bs.recorded_at))))) AS months_since_bs,
    (((cal.year - date_part('year'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, bs.recorded_at)))) * (4)::double precision) + (cal.quarter - date_part('quarter'::text, timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, bs.recorded_at))))) AS quarters_since_bs
   FROM ((blood_sugars bs
     LEFT JOIN reporting_months cal ON ((to_char(timezone(( SELECT current_setting('TIMEZONE'::text) AS current_setting), timezone('UTC'::text, bs.recorded_at)), 'YYYY-MM'::text) <= to_char((cal.month_date)::timestamp with time zone, 'YYYY-MM'::text))))
     JOIN patients p ON (((bs.patient_id = p.id) AND (p.deleted_at IS NULL))))
  WHERE (bs.deleted_at IS NULL)
  ORDER BY bs.patient_id, cal.month_date, bs.recorded_at DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views