[APF Orissa: Phase 2] New Reports UAT Feedback: Children Attended VHND

[kameshbhariya]

• [x] Changes to be made in Child Attended VHND

• [x] 1. Add Filter “Nutrition Status” - SAM, Normal, MAM and Add filter "Growth Faltering" with options GF-1, GF-2, None.
• [x] 2. Add column “Nutritional Status” and "Growth Faltering" in linelist.
• [x] Analysis Link

[adamsanadi6]

Backup: Old Script:

with growth_monitoring as (
    select encounter_date_time,
           program_enrolment_id,
           id,
           "Did the child attended VHND last month",
           ROW_NUMBER()
           OVER (PARTITION BY program_enrolment_id ORDER BY encounter_date_time DESC NULLS LAST) AS visit_number

    from apfodisha.individual_child_growth_monitoring
    where encounter_date_time notnull
      and is_voided = false
  and (
         {% if from_dttm is not none %}
         encounter_date_time > '{{ from_dttm }}' and
         {% endif %}
         {% if to_dttm is not none %}
         encounter_date_time < '{{ to_dttm }}' and
         {% endif %}
         true
         )
)
SELECT child.id                                     AS "Benificiary Id",
       child.registration_date                      AS "Registration date",
       child.first_name                             AS "First name",
       child.last_name                              AS "Last name",
       child.date_of_birth                          AS "DOB",
       DATE_PART('year', AGE(child.date_of_birth)) * 12 +
       DATE_PART('month', AGE(child.date_of_birth)) AS "Age",
       child.gender                                 AS "Gender",
       enrol.enrolment_date_time                    AS "Enrolment date time",
       village."PPK Village",
       village."PVTG Village",
       village."SAMPURNA Village",
       village."Geographically hard to reach village",
       village."Block",
       village."GP",
       village."Village/Hamlet",
       awc."Project/Block"                          AS "Project/Block",
       awc."Sector"                                 AS "Sector",
       awc."AWC"                                    AS "AWC",
       growth_monitoring."Did the child attended VHND last month",
       growth_monitoring.encounter_date_time        AS "Encounter Date Time",
       case
           when enrol.program_exit_date_time is null then 'No'
           when enrol.program_exit_date_time notnull then 'Yes'
           end
                                                    as "Exit"
FROM apfodisha.individual child
         JOIN
     apfodisha.individual_child enrol ON enrol.individual_id = child.id
         AND enrol.is_voided = false
        AND (
                                   {% if to_dttm is not none %}
                                   enrol.enrolment_date_time <= '{{ to_dttm }}' and
                                   {% endif %}
                                   true
                                   )
         LEFT JOIN
     apfodisha.household_individual gs ON gs.member_subject_id = child.id
         AND gs.is_voided = FALSE
         LEFT JOIN
     apfodisha.household house ON gs.group_subject_id = house.id
         AND house.is_voided = FALSE
         LEFT JOIN
     apfodisha.address village ON village.id = child.address_id
         AND village.is_voided = FALSE
         JOIN
     apfodisha.address awc ON awc.uuid = house."AWC Name"
         AND awc.is_voided = FALSE
         LEFT JOIN
     growth_monitoring ON growth_monitoring.program_enrolment_id = enrol.id

WHERE child.is_voided = false
AND (growth_monitoring.visit_number =  1 or growth_monitoring.visit_number is null )

;

---

New Script:

WITH growth_monitoring AS (
    SELECT
        encounter_date_time,
        program_enrolment_id,
        id,
        "Did the child attended VHND last month",
        "Nutritional Status",
                 "Growth Faltering",
        ROW_NUMBER() OVER (PARTITION BY program_enrolment_id ORDER BY encounter_date_time DESC NULLS LAST) AS visit_number
    FROM
        apfodisha.individual_child_growth_monitoring
    WHERE
        encounter_date_time NOTNULL
        AND is_voided = FALSE
        AND (
            {% if from_dttm is not none %}
                encounter_date_time > '{{ from_dttm }}' AND
            {% endif %}
            {% if to_dttm is not none %}
                encounter_date_time < '{{ to_dttm }}' AND
            {% endif %}
            TRUE
        )
)
SELECT 
    child.id AS "Benificiary Id",
    child.registration_date AS "Registration date",
    child.first_name AS "First name",
    child.last_name AS "Last name",
    child.date_of_birth AS "DOB",
    DATE_PART('year', AGE(child.date_of_birth)) * 12 + DATE_PART('month', AGE(child.date_of_birth)) AS "Age",
    child.gender AS "Gender",
    enrol.enrolment_date_time AS "Enrolment date time",
    village."PPK Village",
    village."PVTG Village",
    village."SAMPURNA Village",
    village."Geographically hard to reach village",
    village."Block",
    village."GP",
    village."Village/Hamlet",
    awc."Project/Block" AS "Project/Block",
    awc."Sector" AS "Sector",
    awc."AWC" AS "AWC",
    growth_monitoring."Did the child attended VHND last month",
    CASE 
        WHEN growth_monitoring."Growth Faltering" IS NULL THEN 'None'
        ELSE growth_monitoring."Growth Faltering"
    END AS "Growth Faltering",
        CASE 
      WHEN growth_monitoring."Nutritional Status" IS NULL THEN 'NA'
      ELSE growth_monitoring."Nutritional Status"
        END AS "Nutritional Status",
    growth_monitoring.encounter_date_time AS "Encounter Date Time",
    CASE
      WHEN enrol.program_exit_date_time IS NULL THEN 'No'
      WHEN enrol.program_exit_date_time NOTNULL THEN 'Yes'
        END AS "Exit"
FROM 
    apfodisha.individual child
JOIN apfodisha.individual_child enrol ON 
    enrol.individual_id = child.id
    AND enrol.is_voided = FALSE
    AND (
        {% if to_dttm is not none %}
            enrol.enrolment_date_time <= '{{ to_dttm }}' AND
        {% endif %}
        TRUE
    )
LEFT JOIN apfodisha.household_individual gs ON 
    gs.member_subject_id = child.id
    AND gs.is_voided = FALSE
LEFT JOIN apfodisha.household house ON 
    gs.group_subject_id = house.id
    AND house.is_voided = FALSE
LEFT JOIN apfodisha.address village ON 
    village.id = child.address_id
    AND village.is_voided = FALSE
JOIN apfodisha.address awc ON 
    awc.uuid = house."AWC Name"
    AND awc.is_voided = FALSE
LEFT JOIN growth_monitoring ON 
    growth_monitoring.program_enrolment_id = enrol.id
WHERE 
    child.is_voided = FALSE
    AND (growth_monitoring.visit_number = 1 OR growth_monitoring.visit_number IS NULL);

[adamsanadi6]

Changes made:

1. Added a new column: Nutritional Status, Growth Faltering.
2. Formatted the query for better readability and maintainability.

[nupoorkhandelwal]

@adamsanadi6 add Growth faltering filter, its mentioned in the requirement.

[adamsanadi6]

@dinesh2096 It's fixed