BEULAHEVANJALIN

set role calcutta_kids;

---

Preterm birth rates for CK and non-CK mothers

WITH mothers AS (SELECT individual_relationship.individual_a_id                       AS mother,
                        individual_child."Mother associated with CK during pregnancy" AS "ck_or_non_ck",
                        CASE

BEULAHEVANJALIN

Removing Dependency from Org 1 for Calcutta Kids Implementation

Objective:

The primary objective of this documentation is to remove the dependency of the Calcutta Kids organization's implementation from Org 1.

Steps Taken:

Step 1: Database Restoration

• Task:

BEULAHEVANJALIN

-- Child Death

SELECT COALESCE(a.title, 'Total') AS block,
       COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days <= 28) AS neonatal_death,
       COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 28 AND age_days <= 365) AS infant_death,
       COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 365) AS child_death
FROM address_level a
         LEFT JOIN address_level_type at ON at.id = a.type_id
         LEFT JOIN (
    SELECT

BEULAHEVANJALIN

-- Select the data before the update
select observations ->> '2966afcc-2c07-44cf-8711-3fc23f52a6b5',
       observations ->> '526b0d5d-51cc-4004-8c12-7a6c71c6c516',
       observations ->> 'a043fea3-1658-4b5e-becd-ee55ab305a03',
       observations ->> '1157c7c8-9f27-410d-9115-ef36191fba06'
from individual
where individual.organisation_id = (select id from organisation where name = 'Goonj Revamp');

-- Remove off all the questions from the "Number of Participants" question group.
update individual

BEULAHEVANJALIN

set role aptuat;

select name, uuid
from concept
where uuid in ('dd0bc18d-0661-45d5-84cc-45b1e0d4fba6', 'c2698021-44dc-4b43-a877-3801d4a8c20b',
               '5e37269c-1b30-4766-b48a-893b77fd9a1a', 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23',
               '9a2bf64b-41b1-485e-a6dd-ceb1b75a8397', 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739');

-- Select the count of rows with "Grade" = '6' and "Type of School" = 'Primary' before the update
-- Secondary Grade 6 count should be 0

BEULAHEVANJALIN

WITH disorder_data AS (SELECT 'Mental Illness'                               AS disorder_name,
                              individual.id                                  AS individual_id,
                              EXTRACT(YEAR FROM age(date_of_birth))::INTEGER AS age,
                              gender                                         AS gender,
                              enrolment.id                                   AS enrolment_id,
                              "Reason for exit"
                       FROM jsscpuat_new.individual_mental_illness enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_mental_illness_exit
                                          ON individual.id = individual_mental_illness_exit.individual_id

BEULAHEVANJALIN

set role dfad_trial;


select gender.name                                                 Gender,
       EXTRACT(YEAR FROM AGE(ind.date_of_birth))                   Age,
       enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d' Hemoglobin,
       enc.observations ->> 'd304f306-deca-4418-a9a5-27b04e083623' "Anaemic status",
       case
           when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 12
               AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11