1
WITH follow_up_blood_pressures AS (
2
SELECT DISTINCT ON (((EXTRACT(doy FROM ((bp.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer), bp.facility_id, p.id) p.id AS patient_id,
3
(p.gender)::gender_enum AS patient_gender,
4
bp.id AS visit_id,
5
'BloodPressure'::text AS visit_type,
6
bp.facility_id,
7
bp.user_id,
8
bp.recorded_at AS visited_at,
9
(EXTRACT(doy FROM ((bp.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year
10
FROM (patients p
11
JOIN blood_pressures bp ON (((p.id = bp.patient_id) AND (date_trunc('day'::text, ((bp.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) > date_trunc('day'::text, ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))))))
12
WHERE ((p.deleted_at IS NULL) AND (bp.recorded_at > (CURRENT_TIMESTAMP - '30 days'::interval)))
13
), follow_up_blood_sugars AS (
14
SELECT DISTINCT ON (((EXTRACT(doy FROM ((bs.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer), bs.facility_id, p.id) p.id AS patient_id,
15
(p.gender)::gender_enum AS patient_gender,
16
bs.id AS visit_id,
17
'BloodSugar'::text AS visit_type,
18
bs.facility_id,
19
bs.user_id,
20
bs.recorded_at AS visited_at,
21
(EXTRACT(doy FROM ((bs.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year
22
FROM (patients p
23
JOIN blood_sugars bs ON (((p.id = bs.patient_id) AND (date_trunc('day'::text, ((bs.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) > date_trunc('day'::text, ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))))))
24
WHERE ((p.deleted_at IS NULL) AND (bs.recorded_at > (CURRENT_TIMESTAMP - '30 days'::interval)))
25
), follow_up_prescription_drugs AS (
26
SELECT DISTINCT ON (((EXTRACT(doy FROM ((pd.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer), pd.facility_id, p.id) p.id AS patient_id,
27
(p.gender)::gender_enum AS patient_gender,
28
pd.id AS visit_id,
29
'PrescriptionDrug'::text AS visit_type,
30
pd.facility_id,
31
pd.user_id,
32
pd.device_created_at AS visited_at,
33
(EXTRACT(doy FROM ((pd.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year
34
FROM (patients p
35
JOIN prescription_drugs pd ON (((p.id = pd.patient_id) AND (date_trunc('day'::text, ((pd.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) > date_trunc('day'::text, ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))))))
36
WHERE ((p.deleted_at IS NULL) AND (pd.device_created_at > (CURRENT_TIMESTAMP - '30 days'::interval)))
37
), follow_up_appointments AS (
38
SELECT DISTINCT ON (((EXTRACT(doy FROM ((app.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer), app.creation_facility_id, p.id) p.id AS patient_id,
39
(p.gender)::gender_enum AS patient_gender,
40
app.id AS visit_id,
41
'Appointment'::text AS visit_type,
42
app.creation_facility_id AS facility_id,
43
app.user_id,
44
app.device_created_at AS visited_at,
45
(EXTRACT(doy FROM ((app.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year
46
FROM (patients p
47
JOIN appointments app ON (((p.id = app.patient_id) AND (date_trunc('day'::text, ((app.device_created_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) > date_trunc('day'::text, ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))))))
48
WHERE ((p.deleted_at IS NULL) AND (app.device_created_at > (CURRENT_TIMESTAMP - '30 days'::interval)))
49
), registered_patients AS (
50
SELECT DISTINCT ON (((EXTRACT(doy FROM ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer), p.registration_facility_id, p.id) p.id AS patient_id,
51
(p.gender)::gender_enum AS patient_gender,
52
p.id AS visit_id,
53
'Registration'::text AS visit_type,
54
p.registration_facility_id AS facility_id,
55
p.registration_user_id AS user_id,
56
p.recorded_at AS visited_at,
57
(EXTRACT(doy FROM ((p.recorded_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year
58
FROM patients p
59
WHERE ((p.deleted_at IS NULL) AND (p.recorded_at > (CURRENT_TIMESTAMP - '30 days'::interval)))
60
), all_follow_ups AS (
61
SELECT follow_up_blood_pressures.patient_id,
62
follow_up_blood_pressures.patient_gender,
63
follow_up_blood_pressures.visit_id,
64
follow_up_blood_pressures.visit_type,
65
follow_up_blood_pressures.facility_id,
66
follow_up_blood_pressures.user_id,
67
follow_up_blood_pressures.visited_at,
68
follow_up_blood_pressures.day_of_year
69
FROM follow_up_blood_pressures
70
UNION
71
SELECT follow_up_blood_sugars.patient_id,
72
follow_up_blood_sugars.patient_gender,
73
follow_up_blood_sugars.visit_id,
74
follow_up_blood_sugars.visit_type,
75
follow_up_blood_sugars.facility_id,
76
follow_up_blood_sugars.user_id,
77
follow_up_blood_sugars.visited_at,
78
follow_up_blood_sugars.day_of_year
79
FROM follow_up_blood_sugars
80
UNION
81
SELECT follow_up_prescription_drugs.patient_id,
82
follow_up_prescription_drugs.patient_gender,
83
follow_up_prescription_drugs.visit_id,
84
follow_up_prescription_drugs.visit_type,
85
follow_up_prescription_drugs.facility_id,
86
follow_up_prescription_drugs.user_id,
87
follow_up_prescription_drugs.visited_at,
88
follow_up_prescription_drugs.day_of_year
89
FROM follow_up_prescription_drugs
90
UNION
91
SELECT follow_up_appointments.patient_id,
92
follow_up_appointments.patient_gender,
93
follow_up_appointments.visit_id,
94
follow_up_appointments.visit_type,
95
follow_up_appointments.facility_id,
96
follow_up_appointments.user_id,
97
follow_up_appointments.visited_at,
98
follow_up_appointments.day_of_year
99
FROM follow_up_appointments
100
), all_follow_ups_with_medical_histories AS (
101
SELECT DISTINCT ON (all_follow_ups.day_of_year, all_follow_ups.facility_id, all_follow_ups.patient_id) all_follow_ups.patient_id,
102
all_follow_ups.patient_gender,
103
all_follow_ups.facility_id,
104
mh.diabetes,
105
mh.hypertension,
106
all_follow_ups.user_id,
107
all_follow_ups.visit_id,
108
all_follow_ups.visit_type,
109
all_follow_ups.visited_at,
110
all_follow_ups.day_of_year
111
FROM (all_follow_ups
112
JOIN medical_histories mh ON ((all_follow_ups.patient_id = mh.patient_id)))
113
), registered_patients_with_medical_histories AS (
114
SELECT DISTINCT ON (registered_patients.day_of_year, registered_patients.facility_id, registered_patients.patient_id) registered_patients.patient_id,
115
registered_patients.patient_gender,
116
registered_patients.facility_id,
117
mh.diabetes,
118
mh.hypertension,
119
registered_patients.user_id,
120
registered_patients.visit_id,
121
registered_patients.visit_type,
122
registered_patients.visited_at,
123
registered_patients.day_of_year
124
FROM (registered_patients
125
JOIN medical_histories mh ON ((registered_patients.patient_id = mh.patient_id)))
126
), daily_registered_patients AS (
127
SELECT DISTINCT ON (registered_patients_with_medical_histories.facility_id, (date(((registered_patients_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))) registered_patients_with_medical_histories.facility_id,
128
date(((registered_patients_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) AS visit_date,
129
registered_patients_with_medical_histories.day_of_year,
130
count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) OR (registered_patients_with_medical_histories.diabetes = 'yes'::text))) AS daily_registrations_htn_or_dm,
131
count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text))) AS daily_registrations_htn_and_dm,
132
count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'female'::gender_enum))) AS daily_registrations_htn_and_dm_female,
133
count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'male'::gender_enum))) AS daily_registrations_htn_and_dm_male,
134
count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'transgender'::gender_enum))) AS daily_registrations_htn_and_dm_transgender,
135
(count(*) FILTER (WHERE (registered_patients_with_medical_histories.hypertension = 'yes'::text)) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text)))) AS daily_registrations_htn_only,
136
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'female'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'female'::gender_enum)))) AS daily_registrations_htn_only_female,
137
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'male'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'male'::gender_enum)))) AS daily_registrations_htn_only_male,
138
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'transgender'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'transgender'::gender_enum)))) AS daily_registrations_htn_only_transgender,
139
(count(*) FILTER (WHERE (registered_patients_with_medical_histories.diabetes = 'yes'::text)) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text)))) AS daily_registrations_dm_only,
140
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'female'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'female'::gender_enum)))) AS daily_registrations_dm_only_female,
141
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'male'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'male'::gender_enum)))) AS daily_registrations_dm_only_male,
142
(count(*) FILTER (WHERE ((registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'transgender'::gender_enum))) - count(*) FILTER (WHERE ((registered_patients_with_medical_histories.hypertension = 'yes'::text) AND (registered_patients_with_medical_histories.diabetes = 'yes'::text) AND (registered_patients_with_medical_histories.patient_gender = 'transgender'::gender_enum)))) AS daily_registrations_dm_only_transgender
143
FROM registered_patients_with_medical_histories
144
GROUP BY registered_patients_with_medical_histories.facility_id, (date(((registered_patients_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))), registered_patients_with_medical_histories.day_of_year
145
), daily_follow_ups AS (
146
SELECT DISTINCT ON (all_follow_ups_with_medical_histories.facility_id, (date(((all_follow_ups_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))) all_follow_ups_with_medical_histories.facility_id,
147
date(((all_follow_ups_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))) AS visit_date,
148
all_follow_ups_with_medical_histories.day_of_year,
149
count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) OR (all_follow_ups_with_medical_histories.diabetes = 'yes'::text))) AS daily_follow_ups_htn_or_dm,
150
count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text))) AS daily_follow_ups_htn_and_dm,
151
count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'female'::gender_enum))) AS daily_follow_ups_htn_and_dm_female,
152
count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'male'::gender_enum))) AS daily_follow_ups_htn_and_dm_male,
153
count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'transgender'::gender_enum))) AS daily_follow_ups_htn_and_dm_transgender,
154
(count(*) FILTER (WHERE (all_follow_ups_with_medical_histories.hypertension = 'yes'::text)) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text)))) AS daily_follow_ups_htn_only,
155
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'female'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'female'::gender_enum)))) AS daily_follow_ups_htn_only_female,
156
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'male'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'male'::gender_enum)))) AS daily_follow_ups_htn_only_male,
157
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'transgender'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'transgender'::gender_enum)))) AS daily_follow_ups_htn_only_transgender,
158
(count(*) FILTER (WHERE (all_follow_ups_with_medical_histories.diabetes = 'yes'::text)) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text)))) AS daily_follow_ups_dm_only,
159
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'female'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'female'::gender_enum)))) AS daily_follow_ups_dm_only_female,
160
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'male'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'male'::gender_enum)))) AS daily_follow_ups_dm_only_male,
161
(count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'transgender'::gender_enum))) - count(*) FILTER (WHERE ((all_follow_ups_with_medical_histories.hypertension = 'yes'::text) AND (all_follow_ups_with_medical_histories.diabetes = 'yes'::text) AND (all_follow_ups_with_medical_histories.patient_gender = 'transgender'::gender_enum)))) AS daily_follow_ups_dm_only_transgender
162
FROM all_follow_ups_with_medical_histories
163
GROUP BY all_follow_ups_with_medical_histories.facility_id, (date(((all_follow_ups_with_medical_histories.visited_at AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting)))), all_follow_ups_with_medical_histories.day_of_year
164
), last_30_days AS (
165
SELECT (generate_series((CURRENT_TIMESTAMP - '30 days'::interval), CURRENT_TIMESTAMP, '1 day'::interval))::date AS date
166
)
167
SELECT rf.facility_region_slug,
168
rf.facility_id,
169
rf.facility_region_id,
170
rf.block_region_id,
171
rf.district_region_id,
172
rf.state_region_id,
173
last_30_days.date AS visit_date,
174
(EXTRACT(doy FROM ((last_30_days.date AT TIME ZONE 'UTC'::text) AT TIME ZONE ( SELECT current_setting('TIMEZONE'::text) AS current_setting))))::integer AS day_of_year,
175
COALESCE(daily_registered_patients.daily_registrations_htn_or_dm, (0)::bigint) AS daily_registrations_htn_or_dm,
176
COALESCE(daily_registered_patients.daily_registrations_htn_only, (0)::bigint) AS daily_registrations_htn_only,
177
COALESCE(daily_registered_patients.daily_registrations_htn_only_male, (0)::bigint) AS daily_registrations_htn_only_male,
178
COALESCE(daily_registered_patients.daily_registrations_htn_only_female, (0)::bigint) AS daily_registrations_htn_only_female,
179
COALESCE(daily_registered_patients.daily_registrations_htn_only_transgender, (0)::bigint) AS daily_registrations_htn_only_transgender,
180
COALESCE(daily_registered_patients.daily_registrations_dm_only, (0)::bigint) AS daily_registrations_dm_only,
181
COALESCE(daily_registered_patients.daily_registrations_dm_only_male, (0)::bigint) AS daily_registrations_dm_only_male,
182
COALESCE(daily_registered_patients.daily_registrations_dm_only_female, (0)::bigint) AS daily_registrations_dm_only_female,
183
COALESCE(daily_registered_patients.daily_registrations_dm_only_transgender, (0)::bigint) AS daily_registrations_dm_only_transgender,
184
COALESCE(daily_registered_patients.daily_registrations_htn_and_dm, (0)::bigint) AS daily_registrations_htn_and_dm,
185
COALESCE(daily_registered_patients.daily_registrations_htn_and_dm_male, (0)::bigint) AS daily_registrations_htn_and_dm_male,
186
COALESCE(daily_registered_patients.daily_registrations_htn_and_dm_female, (0)::bigint) AS daily_registrations_htn_and_dm_female,
187
COALESCE(daily_registered_patients.daily_registrations_htn_and_dm_transgender, (0)::bigint) AS daily_registrations_htn_and_dm_transgender,
188
COALESCE(daily_follow_ups.daily_follow_ups_htn_or_dm, (0)::bigint) AS daily_follow_ups_htn_or_dm,
189
COALESCE(daily_follow_ups.daily_follow_ups_htn_only, (0)::bigint) AS daily_follow_ups_htn_only,
190
COALESCE(daily_follow_ups.daily_follow_ups_htn_only_female, (0)::bigint) AS daily_follow_ups_htn_only_female,
191
COALESCE(daily_follow_ups.daily_follow_ups_htn_only_male, (0)::bigint) AS daily_follow_ups_htn_only_male,
192
COALESCE(daily_follow_ups.daily_follow_ups_htn_only_transgender, (0)::bigint) AS daily_follow_ups_htn_only_transgender,
193
COALESCE(daily_follow_ups.daily_follow_ups_dm_only, (0)::bigint) AS daily_follow_ups_dm_only,
194
COALESCE(daily_follow_ups.daily_follow_ups_dm_only_female, (0)::bigint) AS daily_follow_ups_dm_only_female,
195
COALESCE(daily_follow_ups.daily_follow_ups_dm_only_male, (0)::bigint) AS daily_follow_ups_dm_only_male,
196
COALESCE(daily_follow_ups.daily_follow_ups_dm_only_transgender, (0)::bigint) AS daily_follow_ups_dm_only_transgender,
197
COALESCE(daily_follow_ups.daily_follow_ups_htn_and_dm, (0)::bigint) AS daily_follow_ups_htn_and_dm,
198
COALESCE(daily_follow_ups.daily_follow_ups_htn_and_dm_male, (0)::bigint) AS daily_follow_ups_htn_and_dm_male,
199
COALESCE(daily_follow_ups.daily_follow_ups_htn_and_dm_female, (0)::bigint) AS daily_follow_ups_htn_and_dm_female,
200
COALESCE(daily_follow_ups.daily_follow_ups_htn_and_dm_transgender, (0)::bigint) AS daily_follow_ups_htn_and_dm_transgender
201
FROM (((reporting_facilities rf
202
JOIN last_30_days ON (true))
203
LEFT JOIN daily_registered_patients ON (((daily_registered_patients.visit_date = last_30_days.date) AND (daily_registered_patients.facility_id = rf.facility_id))))
204
LEFT JOIN daily_follow_ups ON (((daily_follow_ups.visit_date = last_30_days.date) AND (daily_follow_ups.facility_id = rf.facility_id))))
205
ORDER BY last_30_days.date DESC;