NTD Zambia - Reporting Request

[rkodev]

User request

Would it be possible for you to provide details on the below re ntd zambia?

1. How many households were registered
2. How many QR codes were distributed
3. How many households consented
4. Total number of children under 15 reported

[rkodev]

Number of registered households query

select count(distinct base_entity_id) from core.event_metadata em where event_type in ('Family Registration','Floating Family Registration') and team_id in 
(select distinct o.identifier from team.organization_location ol inner join team.organization o on o.id = ol.organization_id  where ol.plan_id = 100);

select count(distinct for_owner)
from (
select t."json"->>'for' for_owner, t."json"->>'code' code, t."json"->>'status' status, t."json"->>'businessStatus' businessStatus , t."json"->>'groupIdentifier' groupIdentifier
from core.task_metadata tm 
inner join core.task t on t.id  = tm.task_id 
where plan_identifier = 'c6c4f7d2-9cc8-5b63-883f-ca1c436da0b8'
) task_list 
where code in ('RACD Register Family','Floating Family Registration') and status = 'Completed' 
and businessStatus not in  ('Visited, Denied consent','Ineligible','Included in another household');

[rkodev]

How many QR codes were distributed

select count(distinct base_entity_id) from core.event_metadata em where event_type in ('Structure QR') and team_id in 
(select distinct o.identifier from team.organization_location ol inner join team.organization o on o.id = ol.organization_id  where ol.plan_id = 100);

[rkodev]

How many households consented

select count(distinct base_entity_id) from core.event_metadata em where event_type in ('Family Registration','Floating Family Registration') and team_id in 
(select distinct o.identifier from team.organization_location ol inner join team.organization o on o.id = ol.organization_id  where ol.plan_id = 100)
 -
select count (distinct em.base_entity_id) from core.event_metadata em where event_type in ('Family Registration Consent Denied') and team_id in 
(select distinct o.identifier from team.organization_location ol inner join team.organization o on o.id = ol.organization_id  where ol.plan_id = 100);

[rkodev]

Total number of children under 15 reported

select count (distinct cm.base_entity_id)
from core.event_metadata em 
inner join core.client_metadata cm on cm.base_entity_id  = em.base_entity_id 
where event_type in ('Family Member Registration') and em.team_id in 
(select distinct o.identifier from team.organization_location ol inner join team.organization o on o.id = ol.organization_id  where ol.plan_id = 100)
and EXTRACT(YEAR FROM age(cast(cm.birth_date as date))) <= 15;