JSSCP Priority 1 (P1) ETL Migration MCH and Diabetes

[AnjaliBhagabati]

Below are the list of Priority 1 (P1) ETL Migration Reports to be done for the Diabetes and MCH Program Whoever is working on it, kindly update the status as Completed in the Report To ETL sheet: Kindly Update "Completed" status here: https://docs.google.com/spreadsheets/d/15U3mOkcVjHhXlr318uj8DRQNzC6mBOTf-GEPf9SLfTM/edit#gid=431825339

• [x] Antenatal services during pregnancy (Program Name: MCH)
• [x] Disease status fasting (Program Name: Diabetes)
• [x] Patient enrolled with existing complications (Program: Diabetes)
• [x] Patients with any new complications (Program: Diabetes)

[vedfordev]

@AnjaliBhagabati / @Dinesh2019 Diabetes is already done as per Card.

[vedfordev]

@Dinesh2019 report is on prerelease. verified with below query :

--- Mothers who made minimum 4 visits to the health facility (ANC clinic) during pregnancy
with anc as (select anc."TT 1"                                                                          tt1_given,
                    anc."TT 2"                                                                          tt2_given,
                    row_number() over (partition by anc.individual_id order by anc.encounter_date_time) rank,
                    COUNT(*) OVER (PARTITION BY anc.individual_id)                                      number_of_visits,
                    anc.individual_id
             from jsscp.individual_pregnancy_anc_clinic_visit anc
                      join jsscp.individual_pregnancy preg on preg.id = anc.program_enrolment_id
                      join jsscp.individual ind on ind.id = anc.individual_id
                      left join jsscp.individual_pregnancy_abortion abr on ind.id = abr.individual_id
                      left join jsscp.individual_pregnancy_delivery delivery
                                on delivery.individual_id = ind.id and
                                   delivery.encounter_date_time notnull

             where anc.encounter_date_time notnull
               and preg.is_voided = false
               and ind.is_voided = false
               and delivery."Date and time when baby was out" is not null
               and abr."Date and time of abortion" is null
)
select count(*)
from anc
where number_of_visits >=4
  and rank = 1;

-- Mothers who received two doses of TT during present pregnancy
with anc as (select anc."TT 1"                                                                          tt1_given,
                    anc."TT 2"                                                                          tt2_given,
                    row_number() over (partition by anc.individual_id order by anc.encounter_date_time) rank,
                    anc.individual_id
             from jsscp.individual_pregnancy_anc_clinic_visit anc
                      join jsscp.individual_pregnancy preg on preg.id = anc.program_enrolment_id
                      join jsscp.individual ind on ind.id = anc.individual_id
                      left join jsscp.individual_pregnancy_abortion abr on ind.id = abr.individual_id
                      left join jsscp.individual_pregnancy_delivery delivery
                                on delivery.individual_id = ind.id and
                                   delivery.encounter_date_time notnull

             where anc.encounter_date_time notnull
               and preg.is_voided = false
               and ind.is_voided = false
               and delivery."Date and time when baby was out" is not null
               and abr."Date and time of abortion" is null
)
select count(*)
from anc
where tt1_given = 'Yes'
  and tt2_given = 'Yes'
  and rank = 1;