[ ] Refer sheet for description to be added for this report.
[ ] **Folder Structure - Adolescent Sewa Rural New > Service delivery reports**
[ ] Basic logic written here --
with visit_counts as (SELECT GENERATE_SERIES(1, 12) AS ratings),
scheduled_data as (select individual_id,
ROW_NUMBER()
OVER (PARTITION BY program_enrolment_id ORDER BY earliest_visit_date_time ) AS number_of_scheduled_visits
from public.program_encounter
where encounter_type_id in (select id
from public.encounter_type
where is_voided = false
and name = 'Severe Anemia Follow-up')),
completed_data as (select individual_id,
ROW_NUMBER()
OVER (PARTITION BY program_enrolment_id ORDER BY encounter_date_time ) AS number_of_completed_visits
from public.program_encounter
where encounter_type_id in (select id
from public.encounter_type
where is_voided = false
and name = 'Severe Anemia Follow-up')
and encounter_date_time is not null)
select ratings as "Scheduled Visits Count",
count(distinct scheduled_data.individual_id) filter ( where number_of_scheduled_visits = ratings ) as "Scheduled Child Visits Count",
count(distinct scheduled_data.individual_id) filter ( where number_of_completed_visits = ratings ) as "Completed Child Visits Count",
(coalesce(count(distinct scheduled_data.individual_id) filter ( where number_of_completed_visits = ratings ),0) * 100) /
(coalesce(count(distinct scheduled_data.individual_id) filter ( where number_of_scheduled_visits = ratings ),0)) || '%' as "Percentage"
from scheduled_data
left join completed_data on completed_data.individual_id = scheduled_data.individual_id
left join visit_counts vc ON scheduled_data.number_of_scheduled_visits = vc.ratings
GROUP BY 1
ORDER BY 1;``````````
[ ] Basic logic written here --