Pasted

USE caris_db;
SET @start_date= '2018-10-01';
SET @end_date='2019-09-30';
 
SELECT 
    LEFT(patient_code, 8) AS hospital,
    COUNT(*) AS total,
    SUM(IF(gender = 1,
        IF(TIMESTAMPDIFF(MONTH, dob, date) < 12
                AND TIMESTAMPDIFF(MONTH, dob, date) >= 6,
            1,
            0),
        0)) AS m_6_11,
    SUM(IF(gender = 2,
        IF(TIMESTAMPDIFF(MONTH, dob, date) < 12
                AND TIMESTAMPDIFF(MONTH, dob, date) >= 6,
            1,
            0),
        0)) AS f_6_11,
    SUM(IF(gender = 1,
        IF(TIMESTAMPDIFF(MONTH, dob, date) < 60
                AND TIMESTAMPDIFF(MONTH, dob, date) >= 12,
            1,
            0),
        0)) AS m_12_59,
    SUM(IF(gender = 2,
        IF(TIMESTAMPDIFF(MONTH, dob, date) < 60
                AND TIMESTAMPDIFF(MONTH, dob, date) >= 12,
            1,
            0),
        0)) AS f_12_59
FROM
    (SELECT DISTINCT
        patient_code, dob, gender, date
    FROM
        tracking_followup
    LEFT JOIN patient ON patient.id = tracking_followup.id_patient
    LEFT JOIN tracking_infant ON patient.id = tracking_infant.id_patient
    WHERE
        date BETWEEN @start_date AND @end_date
            AND (mid_arm_circumference <12.5)
            AND TIMESTAMPDIFF(MONTH, dob, date) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date) >= 6 UNION SELECT DISTINCT
        odk_child_visit.patient_code,
            dob,
            gender,
            date_of_visit AS date
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    LEFT JOIN tracking_infant ON patient.id = id_patient
    WHERE
        date_of_visit BETWEEN @start_date AND @end_date
            AND (muac <12.5)
            AND TIMESTAMPDIFF(MONTH, dob, date_of_visit) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date_of_visit) >= 6) a
GROUP BY LEFT(patient_code, 8)