Skip to content

Instantly share code, notes, and snippets.

@BEULAHEVANJALIN

BEULAHEVANJALIN/PretermBirthRate.md

Last active February 28, 2024 12:39
Show Gist options
  • Save BEULAHEVANJALIN/7e94f9bc5844a624505a659b9e619a32 to your computer and use it in GitHub Desktop.
Save BEULAHEVANJALIN/7e94f9bc5844a624505a659b9e619a32 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
PretermBirthRate.md 
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
                            WHEN individual_child_birth."Gestational age category at birth" IN
                                 ('Preterm (<28 weeks)', 'Very preterm')
                                THEN 1
                            ELSE 0
                            END                                                       AS is_preterm
                 FROM calcutta_kids.individual
                          LEFT JOIN calcutta_kids.individual_child
                                    ON individual.id = individual_child.individual_id
                          LEFT JOIN calcutta_kids.individual_child_birth
                                    ON individual_child_birth.program_enrolment_id = individual_child.id
                          LEFT JOIN individual_relationship
                                    ON individual_relationship.individual_b_id = individual.id
                          LEFT JOIN individual_relationship_type
                                    ON individual_relationship.relationship_type_id =
                                       individual_relationship_type.id AND
                                       individual_relationship_type.name IN ('mother-son', 'mother-daughter')
                 WHERE individual.is_voided IS FALSE
                   AND individual_child.is_voided IS FALSE
                   AND individual_child_birth.is_voided IS FALSE
                   AND individual_relationship.is_voided IS FALSE
                   AND individual_relationship_type.is_voided IS FALSE),
     mother_details AS (SELECT mother AS                                                                                          mother_id,
                               ROW_NUMBER()
                               OVER (PARTITION BY individual.id order by individual_pregnancy_delivery.encounter_date_time desc ) visit_no,
                               ck_or_non_ck,
                               is_preterm
                        FROM calcutta_kids.individual
                                 JOIN mothers ON mothers.mother = individual.id
                                 LEFT JOIN calcutta_kids.individual_pregnancy_delivery
                                           ON individual_pregnancy_delivery.individual_id = mothers.mother
                                 LEFT JOIN calcutta_kids.address ON individual.address_id = address.id
                        WHERE individual.is_voided IS FALSE
                          AND individual_pregnancy_delivery.is_voided IS FALSE
                          AND address.is_voided IS FALSE
                          AND ck_or_non_ck IS NOT NULL
                          AND individual_pregnancy_delivery."Date of discharge" IS NOT NULL
                          AND individual_pregnancy_delivery."Date of delivery" BETWEEN {{delivery_start_date}} AND {{delivery_end_date}}
     )
SELECT "ck_or_non_ck",
       COUNT(mother_id)                                                           AS total_mothers,
       COUNT(mother_id) FILTER ( WHERE is_preterm = 1 )                           AS preterm_mothers,
       COUNT(mother_id) FILTER ( WHERE is_preterm = 1 )::FLOAT / COUNT(mother_id) AS preterm_rate
FROM mother_details
WHERE visit_no = 1
GROUP BY ck_or_non_ck;

Linelist of mothers

-- Linelist of mothers who have had a preterm birth.
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
                            WHEN individual_child_birth."Gestational age category at birth" IN
                                 ('Preterm (<28 weeks)', 'Very preterm')
                                THEN 1
                            ELSE 0
                            END                                                       AS is_preterm
                 FROM calcutta_kids.individual
                          LEFT JOIN calcutta_kids.individual_child
                                    ON individual.id = individual_child.individual_id
                          LEFT JOIN calcutta_kids.individual_child_birth
                                    ON individual_child_birth.program_enrolment_id = individual_child.id
                          LEFT JOIN individual_relationship
                                    ON individual_relationship.individual_b_id = individual.id
                          LEFT JOIN individual_relationship_type
                                    ON individual_relationship.relationship_type_id =
                                       individual_relationship_type.id AND
                                       individual_relationship_type.name IN ('mother-son', 'mother-daughter')
                 WHERE individual.is_voided IS FALSE
                   AND individual_child.is_voided IS FALSE
                   AND individual_child_birth.is_voided IS FALSE
                   AND individual_relationship.is_voided IS FALSE
                   AND individual_relationship_type.is_voided IS FALSE),
     mother_details AS (SELECT mother                                                   AS                                        mother_id,
                               CONCAT(individual.first_name, ' ', individual.last_name) AS                                        name,
                               EXTRACT(YEAR FROM age(individual.date_of_birth))         AS                                        age,
                               individual."Father/Husband",
                               individual."Household number",
                               individual."myCHI Id",
                               individual."Blood group",
                               individual."Addiction Details",
                               individual."Medical history",
                               individual."Medications",
                               address."Area",
                               mothers."ck_or_non_ck",
                               individual_pregnancy_delivery."Gender of new born1",
                               individual_pregnancy_delivery."Delivered by",
                               individual_pregnancy_delivery."Other delivery complications",
                               individual_pregnancy_delivery."Delivery outcome",
                               individual_pregnancy_delivery."Number of days stayed at the hospital post delivery",
                               individual_pregnancy_delivery."Delivery Complications",
                               individual_pregnancy_delivery."Gender of new born3",
                               individual_pregnancy_delivery."Reason to have birth at home",
                               individual_pregnancy_delivery."Other reason to have birth at home",
                               individual_pregnancy_delivery."Vitamin A given",
                               individual_pregnancy_delivery."Labour time",
                               individual_pregnancy_delivery."Number of babies",
                               individual_pregnancy_delivery."Place of delivery",
                               individual_pregnancy_delivery."Other place of delivery",
                               individual_pregnancy_delivery."Received JSY",
                               individual_pregnancy_delivery."Type of delivery",
                               individual_pregnancy_delivery."Date of delivery",
                               individual_pregnancy_delivery."Date of discharge",
                               ROW_NUMBER()
                               OVER (PARTITION BY individual.id order by individual_pregnancy_delivery.encounter_date_time desc ) visit_no,
                               is_preterm
                        FROM calcutta_kids.individual
                                 JOIN mothers ON mothers.mother = individual.id
                                 LEFT JOIN calcutta_kids.individual_pregnancy_delivery
                                           ON individual_pregnancy_delivery.individual_id = mothers.mother
                                 LEFT JOIN calcutta_kids.address ON individual.address_id = address.id
                        WHERE individual.is_voided IS FALSE
                          AND individual_pregnancy_delivery.is_voided IS FALSE
                          AND address.is_voided IS FALSE
                          AND ck_or_non_ck IS NOT NULL
                          AND individual_pregnancy_delivery."Date of discharge" IS NOT NULL
                          AND individual_pregnancy_delivery."Date of delivery" BETWEEN {{delivery_start_date}} AND {{delivery_end_date}}
     )
SELECT ROW_NUMBER() OVER (ORDER BY mother_id) AS "S.No",
       *
FROM mother_details
WHERE visit_no = 1;

Linelist of mothers who have had a preterm birth.

-- Linelist of mothers who have had a preterm birth.
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
                            WHEN individual_child_birth."Gestational age category at birth" IN
                                 ('Preterm (<28 weeks)', 'Very preterm')
                                THEN 1
                            ELSE 0
                            END                                                       AS is_preterm
                 FROM calcutta_kids.individual
                          LEFT JOIN calcutta_kids.individual_child
                                    ON individual.id = individual_child.individual_id
                          LEFT JOIN calcutta_kids.individual_child_birth
                                    ON individual_child_birth.program_enrolment_id = individual_child.id
                          LEFT JOIN individual_relationship
                                    ON individual_relationship.individual_b_id = individual.id
                          LEFT JOIN individual_relationship_type
                                    ON individual_relationship.relationship_type_id =
                                       individual_relationship_type.id AND
                                       individual_relationship_type.name IN ('mother-son', 'mother-daughter')
                 WHERE individual.is_voided IS FALSE
                   AND individual_child.is_voided IS FALSE
                   AND individual_child_birth.is_voided IS FALSE
                   AND individual_relationship.is_voided IS FALSE
                   AND individual_relationship_type.is_voided IS FALSE),
     mother_details AS (SELECT mother                                                   AS                                        mother_id,
                               CONCAT(individual.first_name, ' ', individual.last_name) AS                                        name,
                               EXTRACT(YEAR FROM age(individual.date_of_birth))         AS                                        age,
                               individual."Father/Husband",
                               individual."Household number",
                               individual."myCHI Id",
                               individual."Blood group",
                               individual."Addiction Details",
                               individual."Medical history",
                               individual."Medications",
                               address."Area",
                               mothers."ck_or_non_ck",
                               individual_pregnancy_delivery."Gender of new born1",
                               individual_pregnancy_delivery."Delivered by",
                               individual_pregnancy_delivery."Other delivery complications",
                               individual_pregnancy_delivery."Delivery outcome",
                               individual_pregnancy_delivery."Number of days stayed at the hospital post delivery",
                               individual_pregnancy_delivery."Delivery Complications",
                               individual_pregnancy_delivery."Gender of new born3",
                               individual_pregnancy_delivery."Reason to have birth at home",
                               individual_pregnancy_delivery."Other reason to have birth at home",
                               individual_pregnancy_delivery."Vitamin A given",
                               individual_pregnancy_delivery."Labour time",
                               individual_pregnancy_delivery."Number of babies",
                               individual_pregnancy_delivery."Place of delivery",
                               individual_pregnancy_delivery."Other place of delivery",
                               individual_pregnancy_delivery."Received JSY",
                               individual_pregnancy_delivery."Type of delivery",
                               individual_pregnancy_delivery."Date of delivery",
                               individual_pregnancy_delivery."Date of discharge",
                               ROW_NUMBER()
                               OVER (PARTITION BY individual.id order by individual_pregnancy_delivery.encounter_date_time desc ) visit_no,
                               is_preterm
                        FROM calcutta_kids.individual
                                 JOIN mothers ON mothers.mother = individual.id
                                 LEFT JOIN calcutta_kids.individual_pregnancy_delivery
                                           ON individual_pregnancy_delivery.individual_id = mothers.mother
                                 LEFT JOIN calcutta_kids.address ON individual.address_id = address.id
                        WHERE individual.is_voided IS FALSE
                          AND individual_pregnancy_delivery.is_voided IS FALSE
                          AND address.is_voided IS FALSE
                          AND ck_or_non_ck IS NOT NULL
                          AND individual_pregnancy_delivery."Date of discharge" IS NOT NULL
                          AND individual_pregnancy_delivery."Date of delivery" BETWEEN {{delivery_start_date}} AND {{delivery_end_date}}
     )
SELECT ROW_NUMBER() OVER (ORDER BY mother_id) AS "S.No",
       *
FROM mother_details
WHERE visit_no = 1
  AND mother_details.is_preterm = 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment