JSSCP Prevalence report

gistfile1.txt

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
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Arthritis'                                    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_arthritis enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_arthritis_exit
                                          ON individual.id = individual_arthritis_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Thyroid disorders'                            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_thyroidism enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_thyroidism_exit
                                          ON individual.id = individual_thyroidism_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Cancer'                                       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_cancer enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_cancer_exit
                                          ON individual.id = individual_cancer_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'COPD'                                         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_copd enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_copd_exit
                                          ON individual.id = individual_copd_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Asthma'                                       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_asthma enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_asthma_exit
                                          ON individual.id = individual_asthma_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Heart disease'                                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_heart_disease enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_heart_disease_exit
                                          ON individual.id = individual_heart_disease_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id
                       UNION ALL
                       SELECT 'Stroke'                                       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_stroke enrolment
                                RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id
                                LEFT JOIN jsscpuat_new.individual_stroke_exit
                                          ON individual.id = individual_stroke_exit.individual_id
                                LEFT JOIN jsscpuat_new.address ON address.id = individual.address_id
                                LEFT JOIN group_subject ON group_subject.member_subject_id = individual.id
                                LEFT JOIN jsscpuat_new.household
                                          ON household.id = group_subject.group_subject_id)
SELECT disorder_name,
       CASE
           WHEN COUNT(DISTINCT individual_id) FILTER (WHERE age > 18) > 0
               THEN
               ROUND(COUNT(DISTINCT individual_id) FILTER (WHERE enrolment_id IS NOT NULL AND age > 18) * 1000::NUMERIC
                         / COUNT(DISTINCT individual_id) FILTER (WHERE age > 18), 2)
           ELSE
               NULL
           END                                                                          AS "Incidence Rate",
       COUNT(DISTINCT individual_id) FILTER (WHERE enrolment_id IS NOT NULL)            AS "Prevalence Count",
       CASE
           WHEN COUNT(DISTINCT individual_id) FILTER (WHERE enrolment_id IS NOT NULL AND gender = 'Female') > 0
               THEN COUNT(DISTINCT individual_id) FILTER (WHERE enrolment_id IS NOT NULL AND gender = 'Male') /
                    COUNT(DISTINCT individual_id) FILTER (WHERE enrolment_id IS NOT NULL AND gender = 'Female')
           ELSE
               NULL
           END                                                                          AS "Sex Ratio",
       COUNT(DISTINCT individual_id)
       FILTER (WHERE enrolment_id IS NOT NULL AND "Reason for exit" = 'Death')::NUMERIC AS "Mortality"
FROM disorder_data
GROUP BY disorder_name;