Skip to content

Instantly share code, notes, and snippets.

@BEULAHEVANJALIN

BEULAHEVANJALIN/ChildDeath.sql

Last active September 12, 2023 07:20
Show Gist options
  • Save BEULAHEVANJALIN/7a2d6ad2514bc99954b1ff8013bc080e to your computer and use it in GitHub Desktop.
Save BEULAHEVANJALIN/7a2d6ad2514bc99954b1ff8013bc080e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ChildDeath.sql
-- Child Death
SELECT COALESCE(a.title, 'Total') AS block,
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days <= 28) AS neonatal_death,
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 28 AND age_days <= 365) AS infant_death,
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 365) AS child_death
FROM address_level a
LEFT JOIN address_level_type at ON at.id = a.type_id
LEFT JOIN (
SELECT
block.title AS block,
program_exit_observations ->> '29238876-fbd8-4f39-b749-edb66024e25e' AS reason_for_exit,
(program_exit_observations ->> '3b269f11-ed0a-4636-8273-da0259783214')::date AS date_of_death,
(individual.date_of_birth)::timestamp without time zone AS date_of_birth,
(program_exit_observations ->> '3b269f11-ed0a-4636-8273-da0259783214')::date - individual.date_of_birth AS age_days
FROM public.individual
LEFT JOIN program_enrolment ON individual.id = program_enrolment.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
WHERE program_exit_observations ->> '29238876-fbd8-4f39-b749-edb66024e25e' = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039'
) exit_join ON a.title = exit_join.block
WHERE at.name = 'Block' AND NOT a.is_voided
GROUP BY ROLLUP (a.title);
Raw
ChildGrowthMonitoring.sql
WITH MergedChildObs AS (select enl.id enl_id,
enl.individual_id ind_id,
enl.enrolment_date_time,
enl.program_exit_date_time,
(enl.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7'::text)::date as discharge_date,
row_number()
OVER (PARTITION BY enl.id ORDER BY coalesce(encounter_date_time, enrolment_date_time) desc) visit_number,
coalesce(enc.observations -> '68a6a336-4a91-468b-9b7d-ff37e637f5b7',
enc.observations ->
'3fb85722-fd53-43db-9e8b-d34767af9f7e')::text as latest_status,
individual.id as ind_id,
(EXTRACT(YEAR FROM age(individual.date_of_birth)) * 12 +
EXTRACT(MONTH FROM age(individual.date_of_birth)) +
(EXTRACT(DAYS FROM age(individual.date_of_birth))) /
30)::NUMERIC AS age_in_months,
block.title block_name
from public.individual
left join program_enrolment enl on individual.id = enl.individual_id
left join program_encounter enc on enl.id = enc.program_enrolment_id
inner join encounter_type et on enc.encounter_type_id = et.id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where et.id = 594)
SELECT 'Dediyapada' AS "Block",
count(*) FILTER ( WHERE age_in_months <= 6) AS current,
count(*) filter (where latest_status is null and age_in_months <= 6) "No visit",
count(*) filter (where latest_status like '%Normal%' and age_in_months <= 6 ) "Normal",
count(*)
filter (where latest_status like '%Severely Underweight%' and age_in_months <= 6 ) "Severely Underweight",
count(*)
filter (where latest_status like '%Moderately Underweight%' and age_in_months <= 6 ) "Moderately Underweight",
count(*) filter (where age_in_months between 6 and 6.5) "Current count at 7 months",
count(*)
filter (where latest_status is null and (age_in_months between 6 and 6.5)) "No visit at 7 months",
count(*)
filter (where latest_status like '%Normal%' and (age_in_months between 6 and 6.5) ) "Normal at 7 months",
count(*) filter (where (latest_status like '%Severely Underweight%' or latest_status like '%SAM%') and
(age_in_months between 6 and 6.5)) "SAM",
count(*) filter (where (latest_status like '%Moderately Underweight%' or latest_status like '%MAM%') and
(age_in_months between 6 and 6.5)) "MAM"
FROM MergedChildObs
WHERE block_name = 'Dediyapada'
AND visit_number = 1
UNION
SELECT 'Jhagadia' AS "Block",
count(*) FILTER ( WHERE age_in_months <= 6) AS current,
count(*) filter (where latest_status is null and age_in_months <= 6 ) "No visit",
count(*) filter (where latest_status like '%Normal%' and age_in_months <= 6 ) "Normal",
count(*)
filter (where latest_status like '%Severely Underweight%' and age_in_months <= 6) "Severely Underweight",
count(*)
filter (where latest_status like '%Moderately Underweight%' and age_in_months <= 6) "Moderately Underweight",
count(*) filter (where age_in_months between 6 and 6.5) "Current count at 7 months",
count(*)
filter (where latest_status is null and (age_in_months between 6 and 6.5)) "No visit at 7 months",
count(*)
filter (where latest_status like '%Normal%' and (age_in_months between 6 and 6.5)) "Normal at 7 months",
count(*) filter (where (latest_status like '%Severely Underweight%' or latest_status like '%SAM%') and
(age_in_months between 6 and 6.5)) "SAM",
count(*) filter (where (latest_status like '%Moderately Underweight%' or latest_status like '%MAM%') and
(age_in_months between 6 and 6.5)) "MAM"
FROM MergedChildObs
WHERE block_name = 'Jhagadia'
AND visit_number = 1
UNION
SELECT 'Netrang' AS "Block",
count(*) FILTER ( WHERE age_in_months <= 6) AS current,
count(*) filter (where latest_status is null and age_in_months <= 6 ) "No visit",
count(*) filter (where latest_status like '%Normal%' and age_in_months <= 6 ) "Normal",
count(*)
filter (where latest_status like '%Severely Underweight%' and age_in_months <= 6) "Severely Underweight",
count(*)
filter (where latest_status like '%Moderately Underweight%' and age_in_months <= 6) "Moderately Underweight",
count(*) filter (where age_in_months between 6 and 6.5) "Current count at 7 months",
count(*)
filter (where latest_status is null and (age_in_months between 6 and 6.5)) "No visit at 7 months",
count(*)
filter (where latest_status like '%Normal%' and (age_in_months between 6 and 6.5)) "Normal at 7 months",
count(*) filter (where (latest_status like '%Severely Underweight%' or latest_status like '%SAM%') and
(age_in_months between 6 and 6.5)) "SAM",
count(*) filter (where (latest_status like '%Moderately Underweight%' or latest_status like '%MAM%') and
(age_in_months between 6 and 6.5)) "MAM"
FROM MergedChildObs
WHERE block_name = 'Netrang'
AND visit_number = 1
UNION
SELECT 'Valia' AS "Block",
count(*) FILTER ( WHERE age_in_months <= 6) AS current,
count(*) filter (where latest_status is null and age_in_months <= 6 ) "No visit",
count(*) filter (where latest_status like '%Normal%' and age_in_months <= 6 ) "Normal",
count(*)
filter (where latest_status like '%Severely Underweight%' and age_in_months <= 6) "Severely Underweight",
count(*)
filter (where latest_status like '%Moderately Underweight%' and age_in_months <= 6) "Moderately Underweight",
count(*) filter (where age_in_months between 6 and 6.5) "Current count at 7 months",
count(*)
filter (where latest_status is null and (age_in_months between 6 and 6.5)) "No visit at 7 months",
count(*)
filter (where latest_status like '%Normal%' and (age_in_months between 6 and 6.5)) "Normal at 7 months",
count(*) filter (where (latest_status like '%Severely Underweight%' or latest_status like '%SAM%') and
(age_in_months between 6 and 6.5)) "SAM",
count(*) filter (where (latest_status like '%Moderately Underweight%' or latest_status like '%MAM%') and
(age_in_months between 6 and 6.5)) "MAM"
FROM MergedChildObs
WHERE block_name = 'Valia'
AND visit_number = 1
UNION
SELECT 'Total' AS "Block",
count(*) FILTER (WHERE age_in_months <= 6) AS current,
count(*) filter (where latest_status is null and age_in_months <= 6 ) "No visit",
count(*) filter (where latest_status like '%Normal%' and age_in_months <= 6 ) "Normal",
count(*)
filter (where latest_status like '%Severely Underweight%' and age_in_months <= 6) "Severely Underweight",
count(*)
filter (where latest_status like '%Moderately Underweight%' and age_in_months <= 6) "Moderately Underweight",
count(*) filter (where age_in_months between 6 and 6.5) "Current count at 7 months",
count(*)
filter (where latest_status is null and (age_in_months between 6 and 6.5)) "No visit at 7 months",
count(*) filter (where latest_status like '%Normal%' and (age_in_months between 6 and 6.5)) "Normal at 7 months",
count(*) filter (where (latest_status like '%Severely Underweight%' or latest_status like '%SAM%') and
(age_in_months between 6 and 6.5)) "SAM",
count(*) filter (where (latest_status like '%Moderately Underweight%' or latest_status like '%MAM%') and
(age_in_months between 6 and 6.5)) "MAM"
FROM MergedChildObs
WHERE block_name in ('Valia', 'Dediyapada', 'Jhagadia', 'Netrang')
AND visit_number = 1;
Raw
ChildGrowthMonitoring7MonthsTo5Years.sql
WITH merged_child_obs AS (SELECT address."Block",
(EXTRACT(year FROM age(individual.date_of_birth)) * 12 +
EXTRACT(month FROM age(individual.date_of_birth)) +
(EXTRACT(days FROM age(individual.date_of_birth))) /
30)::numeric age_in_months,
followup.observations ->> '3fb85722-fd53-43db-9e8b-d34767af9f7e' nutritional_status,
single_select_coded(followup.observations ->> 'db23114a-899f-4904-b43f-0c8356960a25') cmtc_referred,
enrolment.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7' "Date of discharge from SNCU",
ROW_NUMBER()
OVER (PARTITION BY enrolment.id ORDER BY COALESCE(followup.encounter_date_time,
enrolment.enrolment_date_time) DESC) AS visit_number
FROM program_enrolment enrolment
LEFT JOIN individual ON individual.id = enrolment.individual_id
LEFT JOIN program_encounter followup
ON enrolment.id = followup.program_enrolment_id AND encounter_type_id = 594
LEFT JOIN sncu.address ON individual.address_id = address.id),
data AS (SELECT "Block",
CASE
WHEN nutritional_status IS NULL AND visit_number = 1 THEN 'Yes'
ELSE 'No' END AS no_visit,
CASE
WHEN nutritional_status LIKE '%Normal%' AND visit_number = 1 THEN 'Yes'
ELSE 'No' END AS normal_visit_one,
CASE
WHEN nutritional_status LIKE '%SAM%' AND visit_number = 1 THEN 'Yes'
ELSE 'No' END AS sam_visit_one,
CASE
WHEN nutritional_status LIKE '%MAM%' AND visit_number = 1 THEN 'Yes'
ELSE 'No' END AS mam_visit_one,
CASE
WHEN nutritional_status LIKE '%Normal%' AND visit_number = 2 THEN 'Yes'
ELSE 'No' END AS normal_visit_two,
CASE
WHEN nutritional_status LIKE '%SAM%' AND visit_number = 2 THEN 'Yes'
ELSE 'No' END AS sam_visit_two,
CASE
WHEN nutritional_status LIKE '%MAM%' AND visit_number = 2 THEN 'Yes'
ELSE 'No' END AS mam_visit_two,
visit_number,
cmtc_referred
FROM merged_child_obs child
WHERE age_in_months BETWEEN 7 AND 60
AND "Block" IS NOT NULL)
SELECT COALESCE("Block", 'Total') AS "Block",
COUNT(*) FILTER (WHERE visit_number = 1) AS "Current",
COUNT(*) FILTER (WHERE no_visit = 'Yes') AS "NO Visit Data",
COUNT(*) FILTER (WHERE normal_visit_one = 'Yes') AS "Normal",
COUNT(*) FILTER (WHERE sam_visit_one = 'Yes') AS "SAM",
COUNT(*) FILTER (WHERE mam_visit_one = 'Yes') AS "MAM",
COUNT(*) FILTER (WHERE sam_visit_two = 'Yes' AND sam_visit_one = 'No') AS "Removed from SAM",
COUNT(*) FILTER (WHERE sam_visit_one = 'Yes' AND sam_visit_two = 'No') AS "Added in SAM",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%Sewa Rural%' AND visit_number = 1) AS "Sewa Rural",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%CHC%' AND visit_number = 1) AS "CHC",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%PHC%' AND visit_number = 1) AS "PHC",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%Private Hospital%' AND visit_number = 1) AS "Private Hospital",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%District hospital%' AND visit_number = 1) AS "District hospital",
COUNT(*) FILTER (WHERE cmtc_referred LIKE '%Medical College%' AND visit_number = 1) AS "Medical College",
COUNT(*) FILTER (WHERE cmtc_referred IS NOT NULL AND visit_number = 1) AS "Total"
FROM data
GROUP BY ROLLUP ("Block");
Raw
KMC_Care.sql
with agg_data as (select block,
sum(case when kmc_answer_yes > 0 and kmc_answer_no = 0 then 1 else 0 end) as kmc_given,
sum(case when kmc_answer_no > 0 then 1 else 0 end) as left_wo_kmc
from (select enc.program_enrolment_id,
"Block" block,
row_number() OVER (PARTITION BY enl.id ORDER BY coalesce
(enc.encounter_date_time, enl.enrolment_date_time) desc) visit_number,
count(distinct enc.individual_id) filter ( where
(enc.observations ->> 'bab98eac-14a5-43c4-80ff-ccdb8c3ddf1b')::numeric < 2.5 and
single_select_coded(enc.observations ->> 'd21d1844-362b-426c-a5d4-c45ab5fc1690') = 'Yes') kmc_answer_yes,
count(distinct enc.individual_id) filter ( where
(enc.observations ->> 'bab98eac-14a5-43c4-80ff-ccdb8c3ddf1b')::numeric < 2.5 and
single_select_coded(enc.observations ->> 'd21d1844-362b-426c-a5d4-c45ab5fc1690') = 'No') kmc_answer_no
from program_encounter enc
left join encounter_type et on enc.encounter_type_id = et.id and et.id = 595
left join program_enrolment enl on enl.id = enc.program_enrolment_id
left join public.individual individual on individual.id = enl.individual_id
left join address on individual.address_id = address.id
where enc.earliest_visit_date_time is not null
and ((enl.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7')::timestamp +
interval '330 minutes') + INTERVAL '59 days' > '2023-09-08'::date
and ((enl.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7')::timestamp +
interval '330 minutes') between '2023-07-11'::date and '2023-09-08'::date
group by enc.program_enrolment_id, "Block", enc.encounter_date_time, enl.enrolment_date_time, enl.id) foo
where visit_number = 1
group by block),
enrolment_data as (select enc.observations ->> 'bab98eac-14a5-43c4-80ff-ccdb8c3ddf1b' enc_weight,
"Block",
enl.observations ->> 'c82cd1c8-d0a9-4237-b791-8d64e52b6c4a' enl_weight,
row_number() OVER (PARTITION BY enl.id ORDER BY coalesce
(enc.encounter_date_time, enl.enrolment_date_time) desc) visit_number
from program_enrolment enl
left join program_encounter enc on enl.id = enc.program_enrolment_id
left join encounter_type
on enc.encounter_type_id = encounter_type.id and encounter_type.id = 595
left join public.individual individual on individual.id = enl.individual_id
left join sncu.address on individual.address_id = address.id
where ((enl.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7')::timestamp +
interval '330 minutes') + INTERVAL '59 days' > '2023-09-08'::date
and ((enl.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7')::timestamp +
interval '330 minutes') between '2023-07-11'::date and '2023-09-08'::date)
select block,
kmc_given,
nullif(kmc_given / (nullif(children_with_lt_two_point_five_weight, 0)) * 100, 0) kmc_given_proportion,
left_wo_kmc,
nullif(left_wo_kmc / (nullif(children_with_lt_two_point_five_weight, 0)) * 100, 0) left_wo_kmc_proportion,
children_with_lt_two_point_five_weight low_weight_total
from (select coalesce(a.title, 'Total') block,
sum(coalesce(kmc_given, 0)) kmc_given,
sum(coalesce(left_wo_kmc, 0)) left_wo_kmc,
(select count(*)
from enrolment_data
where coalesce(enc_weight, enl_weight)::numeric < 2.5
and visit_number = 1
and ("Block" = a.title or a.title isnull)) children_with_lt_two_point_five_weight
from address_level a
join address_level_type at on a.type_id = at.id and at.name = 'Block'
left join agg_data on agg_data.block = a.title
where a.is_voided = false
group by rollup (a.title)
order by a.title) foo;
Raw
Registration_Form_based_report.sql
-- GENDER
with reg_data as (select distinct individual.id,
gender.name gender,
individual.observations ->> 'a01c2055-7483-4a19-98c1-80fdf955b50c' as totalMembers
from public.individual
left join gender on individual.gender_id = gender.id
LEFT JOIN program_enrolment enrolment on individual.id = enrolment.individual_id
LEFT JOIN sncu.address on individual.address_id = address.id
where not individual.is_voided)
select unnest(ARRAY ['Male',
'Female',
'Others',
'Total']) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where gender = 'Male' ), 0),
coalesce(count(*) filter ( where gender = 'Female' ), 0),
coalesce(count(*) filter ( where gender = 'Other' ), 0),
coalesce(count(*), 0)]) as "Count"
from reg_data;
-- NUMBER OF FAMILY MEMBER
WITH reg_data AS (SELECT DISTINCT individual.id,
gender.name AS gender,
(individual.observations ->> 'a01c2055-7483-4a19-98c1-80fdf955b50c')::numeric AS totalMembers
FROM public.individual
LEFT JOIN gender ON individual.gender_id = gender.id
LEFT JOIN program_enrolment enrolment ON individual.id = enrolment.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
WHERE NOT individual.is_voided)
SELECT CASE
WHEN totalMembers <= 10 THEN totalMembers::TEXT
ELSE '>10'
END AS "Indicator",
COUNT(*) AS "Count"
FROM reg_data
GROUP BY "Indicator"
UNION ALL
SELECT 'Total' AS "Indicator",
COUNT(*) AS "Count"
FROM reg_data
GROUP BY "Indicator";
-- VERY POOR FAMILIY
with reg_data as (select distinct individual.id,
gender.name gender,
(individual.observations ->> 'a01c2055-7483-4a19-98c1-80fdf955b50c')::numeric AS totalMembers,
single_select_coded(individual.observations ->> '8eb5a6ce-7b8a-45cc-a066-fcceca3708f7') AS "Ration card",
single_select_coded(individual.observations ->> 'ba25ac4c-784a-4723-8e15-a965a0d63b50') AS "Caste",
single_select_coded(individual.observations ->> 'b9c9d807-7064-46fd-8dc7-1640345dc8cb') AS "Religion",
single_select_coded(individual.observations ->> '89fe78b2-20a9-45f1-90e3-119a7bc95ce3') AS "Very poor family"
from public.individual individual
LEFT JOIN gender on individual.gender_id = gender.id
LEFT JOIN program_enrolment enrolment on individual.id = enrolment.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where 1 = 1
and not individual.is_voided)
select unnest(ARRAY ['Yes', 'No', 'Total']) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where "Very poor family" = 'Yes' ), 0),
coalesce(count(*) filter ( where "Very poor family" = 'No' ), 0),
coalesce(count(*), 0)]) as "Count"
from reg_data;
Raw
Registration_of_children.sql
with pregnancy_data as (SELECT individual.id "Ind.Id",
"Block" "Ind.block",
"PHC" "Ind.phc",
"Subcenter" "Ind.subcenter",
"Village" "Ind.village",
enrolment.enrolment_date_time "Enl.enrolment_date_time",
enrolment.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7' AS "Date of discharge from SNCU",
case
when enrolment.program_exit_date_time is null then 'No'
else 'Yes' end isExited
from program_enrolment enrolment
left join public.individual individual on individual.id = enrolment.individual_id
left join sncu.address on address.id = individual.address_id)
select coalesce("Ind.block", 'Total') block,
nullif(count(*), 0) "Total"
from pregnancy_data
where "Ind.block" is not null
group by rollup ("Ind.block");
Raw
SNCU_form_based_reports.sql
-- WEIGHT AT TIME OF DISCHARGE
with sncu_data as (select distinct enl.id,
(enl.observations ->> 'a881b2a4-a63f-47d1-b60f-915a95fab40f')::numeric "Weight at the time of Discharge",
case when enl.program_exit_date_time is null then 'No' else 'Yes' end exit_status
from program_enrolment enl
left join public.individual individual on individual.id = enl.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where not enl.is_voided
and not individual.is_voided)
select unnest(ARRAY [ '< 1 kg',
'1 to 1.49 kg',
'1.5-1.99 kg',
'2.0- 2.49 kg',
'>= 2.5 kg',
'Not answered',
'Total']) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where "Weight at the time of Discharge" < 1 ), 0),
coalesce(count(*) filter ( where "Weight at the time of Discharge" between 1 and 1.499), 0),
coalesce(count(*) filter ( where "Weight at the time of Discharge" between 1.5 and 1.999 ), 0),
coalesce(count(*) filter ( where "Weight at the time of Discharge" between 2 and 2.499), 0),
coalesce(count(*) filter ( where "Weight at the time of Discharge" >= 2.5 ), 0),
coalesce(count(*) filter ( where "Weight at the time of Discharge" is null ), 0),
coalesce(count(*), 0)]) as "Count"
from sncu_data;
-- DISCHARGE CRITERIA
with sncu_data as (select distinct enl.id,
(enl.observations ->> 'a881b2a4-a63f-47d1-b60f-915a95fab40f')::numeric "Weight at the time of Discharge",
single_select_coded(enl.observations ->> '031ce79b-dc20-4137-b9e1-b5a7a7b97042') "Discharge criteria",
case when enl.program_exit_date_time is null then 'No' else 'Yes' end exit_status
from program_enrolment enl
left join public.individual individual on individual.id = enl.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where not enl.is_voided
and not individual.is_voided)
select unnest(ARRAY [ 'Discharged',
'Referred to higher center',
'Discharged on request',
'Discharged against advice',
'Not answered',
'Total']) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where "Discharge criteria" = 'Discharged' ), 0),
coalesce(count(*) filter ( where "Discharge criteria" = 'Referred to higher center'), 0),
coalesce(count(*) filter ( where "Discharge criteria" = 'Discharged on request' ), 0),
coalesce(count(*) filter ( where "Discharge criteria" = 'Discharged against advice'), 0),
coalesce(count(*) filter ( where "Discharge criteria" is null), 0),
coalesce(count(*), 0)]) as "Count"
from sncu_data;
-- DID CHILD CRY IMMEDIATELY AFTER BIRTH
with sncu_data as (select distinct enl.id,
(enl.observations ->> 'a881b2a4-a63f-47d1-b60f-915a95fab40f')::numeric "Weight at the time of Discharge",
single_select_coded(enl.observations ->> '031ce79b-dc20-4137-b9e1-b5a7a7b97042') "Discharge criteria",
single_select_coded(enl.observations ->> 'f1eb8ffc-f6b0-4089-9713-041041ea2c37') "Did child cry immedately after birth",
case when enl.program_exit_date_time is null then 'No' else 'Yes' end exit_status
from program_enrolment enl
left join public.individual individual on individual.id = enl.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where not enl.is_voided
and not individual.is_voided)
select unnest(ARRAY [ 'Immediately',
'Cried with efforts',
'No Cry',
'Not answered',
'Total']) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where "Did child cry immedately after birth" = 'Immediately' ),0),
coalesce(count(*) filter ( where "Did child cry immedately after birth" = 'Cried with efforts'),0),
coalesce(count(*) filter ( where "Did child cry immedately after birth" = 'No Cry' ),0),
coalesce(count(*) filter ( where "Did child cry immedately after birth" is null ),0),
coalesce(count(*) ,0)]) as "Count"
from sncu_data;
-- COGNENTIAL ANOMALY
with sncu_data as (select distinct enl.id,
single_select_coded(individual.observations ->> 'a154508a-9d8b-49d9-9d78-b61cbc9daf7f') "Congenital abnormality",
case when enl.program_exit_date_time is null then 'No' else 'Yes' end exit_status
from program_enrolment enl
left join public.individual individual on individual.id = enl.individual_id
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where not enl.is_voided
and not individual.is_voided)
select unnest(ARRAY [ 'Club foot',
'Congenital heart abnormality',
'Cleft Lip',
'Cleft Palate',
'Other',
'NA',
'Total'
]) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where "Congenital abnormality" = 'Club foot' ),0),
coalesce(count(*) filter ( where "Congenital abnormality" = 'Congenital abnormality of heart'),0),
coalesce(count(*) filter ( where "Congenital abnormality" = 'Cleft Lip' ),0),
coalesce(count(*) filter ( where "Congenital abnormality" = 'Cleft Palate' ),0),
coalesce(count(*) filter ( where "Congenital abnormality" = 'Other' ),0),
coalesce(count(*) filter ( where "Congenital abnormality" is null ),0),
coalesce(count(*) ,0)]) as "Count"
from sncu_data;
-- CHILD'S FIRST VISIT
with pnc_data as ( select program_enrolment_id,
encounter_date_time,
row_number() over (partition by program_enrolment_id order by encounter_date_time) row_number
from program_encounter enc
where enc.encounter_type_id = 595 and enc.encounter_date_time is not null),
sncu_data as (
select distinct enl.id,
pnc.encounter_date_time as encounter_date_time,
enl.enrolment_date_time,
DATE_PART('day', pnc.encounter_date_time - enl.enrolment_date_time) as childFirstVisitDay,
case when enl.program_exit_date_time is null then 'No' else 'Yes' end exit_status
from program_enrolment enl
left join public.individual individual on individual.id = enl.individual_id
left join pnc_data pnc on pnc.program_enrolment_id = enl.id and row_number = 1
LEFT JOIN address_level village ON individual.address_id = village.id
LEFT JOIN address_level subcenter ON village.parent_id = subcenter.id
LEFT JOIN address_level phc ON subcenter.parent_id = phc.id
LEFT JOIN address_level block ON phc.parent_id = block.id
where not enl.is_voided and not individual.is_voided)
select unnest(ARRAY [ '<10 days',
'10-14 days',
'15-30 Days',
'>30 days',
'PNC visit not done',
'Total'
]) as "Indicator",
unnest(ARRAY [ coalesce(count(*) filter ( where childFirstVisitDay < 10 ),0),
coalesce(count(*) filter ( where childFirstVisitDay between 10 and 14),0),
coalesce(count(*) filter ( where childFirstVisitDay between 15 and 30),0),
coalesce(count(*) filter ( where childFirstVisitDay > 30 ),0),
coalesce(count(*) filter ( where encounter_date_time is null ),0),
coalesce(count(*) ,0)]) as "Count"
from sncu_data;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment