vindeolal
commented
2 years ago @nupoorkhandelwal Tasks done,
- Query is written and attached below.
Remaining,
- Right now prod has data for 9 months, so we need to add some more data to see other columns and test it out.
- Using filters in Jasper.
Query:
with enrolment_data as (
select id,
individual_id,
enrolment_date_time,
program_exit_date_time,
(observations ->> 'c82cd1c8-d0a9-4237-b791-8d64e52b6c4a')::numeric as birth_weight,
case when program_exit_date_time is null then 'Non exited' else 'Exited' end as enrolment_status
from program_enrolment
where not is_voided
),
followup_data as (
select enl.id as enrolment_id,
extract(year from age(encounter_date_time, enrolment_date_time)) * 12 +
extract(month from age(encounter_date_time, enrolment_date_time)) as month_from_enrolment,
(enc.observations ->> 'bab98eac-14a5-43c4-80ff-ccdb8c3ddf1b')::numeric as weight,
enc.observations ->> '68a6a336-4a91-468b-9b7d-ff37e637f5b7' as wfas,
enc.observations ->> '3fb85722-fd53-43db-9e8b-d34767af9f7e' as nutritional_status
from program_encounter enc
join program_enrolment enl on enc.program_enrolment_id = enl.id
where encounter_type_id = (select id from encounter_type where name = 'Child Followup')
and not enc.is_voided
and not enl.is_voided
and enc.encounter_date_time notnull
)
select concat(first_name, ' ', last_name) as "Name",
g.name as "Gender",
extract('year' from age(date_of_birth::date)) || ' Year ' || extract('month' from age(date_of_birth::date)) ||
' Month ' as "Age",
concat(block.title, ',', phc.title, ',', subcenter.title, ',', village.title) as "Address",
enrolment_status as "Enrolment status",
enrolment_date_time::date as "Enrolment date",
program_exit_date_time::date as "Exit date",
birth_weight as "Birth weight",
coalesce(month_6.weight, month_7.weight) as weight_6,
coalesce(month_6.wfas, month_7.wfas) as wfas_6,
coalesce(month_12.weight, month_13.weight) as weight_12,
coalesce(month_12.wfas, month_13.wfas) as wfas_12,
coalesce(month_12.nutritional_status, month_13.nutritional_status) as ns_12,
coalesce(month_18.weight, month_19.weight) as weight_18,
coalesce(month_18.wfas, month_19.wfas) as wfas_18,
coalesce(month_18.nutritional_status, month_19.nutritional_status) as ns_18,
coalesce(month_24.weight, month_25.weight) as weight_24,
coalesce(month_24.wfas, month_25.wfas) as wfas_24,
coalesce(month_24.nutritional_status, month_25.nutritional_status) as ns_24,
coalesce(month_30.weight, month_31.weight) as weight_30,
coalesce(month_30.wfas, month_31.wfas) as wfas_30,
coalesce(month_30.nutritional_status, month_31.nutritional_status) as ns_30,
coalesce(month_36.weight, month_37.weight) as weight_36,
coalesce(month_36.wfas, month_37.wfas) as wfas_36,
coalesce(month_36.nutritional_status, month_37.nutritional_status) as ns_36,
coalesce(month_42.weight, month_43.weight) as weight_42,
coalesce(month_42.wfas, month_43.wfas) as wfas_42,
coalesce(month_42.nutritional_status, month_43.nutritional_status) as ns_42,
coalesce(month_48.weight, month_49.weight) as weight_48,
coalesce(month_48.wfas, month_49.wfas) as wfas_48,
coalesce(month_48.nutritional_status, month_49.nutritional_status) as ns_48,
coalesce(month_54.weight, month_55.weight) as weight_54,
coalesce(month_54.wfas, month_55.wfas) as wfas_54,
coalesce(month_54.nutritional_status, month_55.nutritional_status) as ns_54,
month_60.weight as weight_60,
month_60.wfas as wfas_60,
month_60.nutritional_status as ns_60
from individual i
join gender g on g.id = i.gender_id
join address_level village on village.id = i.address_id
join address_level subcenter on subcenter.id = village.parent_id
join address_level phc on phc.id = subcenter.parent_id
join address_level block on block.id = phc.parent_id
left join enrolment_data enl on i.id = enl.individual_id
left join followup_data month_6 on month_6.enrolment_id = enl.id and month_6.month_from_enrolment = 6
left join followup_data month_7 on month_7.enrolment_id = enl.id and month_7.month_from_enrolment = 7
left join followup_data month_12 on month_12.enrolment_id = enl.id and month_12.month_from_enrolment = 12
left join followup_data month_13 on month_13.enrolment_id = enl.id and month_13.month_from_enrolment = 13
left join followup_data month_18 on month_18.enrolment_id = enl.id and month_18.month_from_enrolment = 18
left join followup_data month_19 on month_19.enrolment_id = enl.id and month_19.month_from_enrolment = 19
left join followup_data month_24 on month_24.enrolment_id = enl.id and month_24.month_from_enrolment = 24
left join followup_data month_25 on month_25.enrolment_id = enl.id and month_25.month_from_enrolment = 25
left join followup_data month_30 on month_30.enrolment_id = enl.id and month_30.month_from_enrolment = 30
left join followup_data month_31 on month_31.enrolment_id = enl.id and month_31.month_from_enrolment = 31
left join followup_data month_36 on month_36.enrolment_id = enl.id and month_36.month_from_enrolment = 36
left join followup_data month_37 on month_37.enrolment_id = enl.id and month_37.month_from_enrolment = 37
left join followup_data month_42 on month_42.enrolment_id = enl.id and month_42.month_from_enrolment = 42
left join followup_data month_43 on month_43.enrolment_id = enl.id and month_43.month_from_enrolment = 43
left join followup_data month_48 on month_48.enrolment_id = enl.id and month_48.month_from_enrolment = 48
left join followup_data month_49 on month_49.enrolment_id = enl.id and month_49.month_from_enrolment = 49
left join followup_data month_54 on month_54.enrolment_id = enl.id and month_54.month_from_enrolment = 54
left join followup_data month_55 on month_55.enrolment_id = enl.id and month_55.month_from_enrolment = 55
left join followup_data month_60 on month_60.enrolment_id = enl.id and month_60.month_from_enrolment = 60
where not i.is_voided;
garimadosar5
https://docs.google.com/spreadsheets/d/1K5421m3LjHgrhFgpLr_3v-QZrS9I0MT5/edit#gid=383435575
Filter same as old report
Logic is updated as - https://docs.google.com/spreadsheets/d/1K5421m3LjHgrhFgpLr_3v-QZrS9I0MT5/edit#gid=383435575&range=O2:Q2