The mismatch of individuals in group subject data and other forms

[BEULAHEVANJALIN]

Support ticket: https://avni.freshdesk.com/a/tickets/3988

---

On doing longitudinal exports, the number of households and individuals in the group subject data does not match the household registration data and individual registration data.

[BEULAHEVANJALIN]

set role yenepoya;

-- Total household registered
select count(distinct individual.id)
from public.individual
         join subject_type on individual.subject_type_id = subject_type.id and subject_type.name = 'Household'
where individual.is_voided is false;
-- 1744 total; 1733 non-voided

select registration_date
from public.individual
         join subject_type on individual.subject_type_id = subject_type.id and subject_type.name = 'Household'
order by registration_date; -- They've been registering Households since 2021-01-25

select count(distinct group_subject.group_subject_id)
from group_subject
         join group_role gr on group_subject.group_role_id = gr.id
where gr.role = 'Head of household'; -- 1688 households in group subject table

select 1744 - 1688; -- =56

select *
from public.individual
where id in (select distinct individual.id
             from public.individual
                      join subject_type on individual.subject_type_id = subject_type.id and subject_type.name = 'Household'
             except
             select distinct group_subject.group_subject_id
             from group_subject
                      join group_role gr on group_subject.group_role_id = gr.id
             where gr.role = 'Head of household'); -- 56 individuals were there in the household registration table but not in the group subject table.