Pasted

USE caris_db;
SET  @start_date = '2018-10-01';
SET @end_date = '2019-06-30';
SELECT 
    z.patient_code,
    caseId,
    x.*,
    odk.date_of_visit AS odk_date_of_visit,
    odk.type_of_visit AS odk_type_of_visit,
    odk.weight_kg AS odk_weight_kg,
    odk.Height AS odk_Height,
    odk.muac AS odk_muac,
    last_muac AS odk_last_muac,
    last_weight AS odk_last_weight,
    f.date AS hiv_date_of_visit,
    f.mid_arm_circumference AS hiv_muac,
    f.weight AS hiv_weight,
    f.Height AS hiv_Height,
    hiv_last_muac,
    hiv_last_muac_date
FROM
    (SELECT 
        dob,
            COALESCE(followup.id_patient, club.id_patient, specimen.id_patient, arv.id_patient, odk.id_patient) AS id_patient,
            IF(followup.id_patient IS NOT NULL, 'yes', 'no') AS followup,
            IF(club.id_patient IS NOT NULL, 'yes', 'no') AS club,
            IF(specimen.id_patient IS NOT NULL, 'yes', 'no') AS specimen,
            IF(arv.id_patient IS NOT NULL, 'yes', 'no') AS arv,
            IF(odk.id_patient IS NOT NULL, 'yes', 'no') AS odk
    FROM
        tracking_infant
    LEFT JOIN (SELECT DISTINCT
        tracking_followup.id_patient
    FROM
        tracking_followup
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = tracking_followup.id_patient
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date) > 5
            AND date BETWEEN @start_date AND @end_date) followup ON tracking_infant.id_patient = followup.id_patient
    LEFT JOIN (SELECT DISTINCT
        session.id_patient
    FROM
        session
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = session.id_patient
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date) > 5
            AND date BETWEEN @start_date AND @end_date
            AND is_present = 1) club ON club.id_patient = followup.id_patient
    LEFT JOIN (SELECT DISTINCT
        testing_specimen.id_patient
    FROM
        testing_specimen
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = testing_specimen.id_patient
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date_blood_taken) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date_blood_taken) > 5
            AND date_blood_taken BETWEEN @start_date AND @end_date) specimen ON specimen.id_patient = tracking_infant.id_patient
    LEFT JOIN (SELECT DISTINCT
        tracking_regime.id_patient
    FROM
        tracking_regime
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = tracking_regime.id_patient
    WHERE
        TIMESTAMPDIFF(MONTH, dob, start_date) < 60
            AND TIMESTAMPDIFF(MONTH, dob, start_date) > 5
            AND start_date BETWEEN @start_date AND @end_date) arv ON arv.id_patient = tracking_infant.id_patient
    LEFT JOIN (SELECT DISTINCT
        patient.id AS id_patient
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = patient.id
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date_of_visit) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date_of_visit) > 5
            AND date_of_visit BETWEEN @start_date AND @end_date) odk ON odk.id_patient = tracking_infant.id_patient) x
        LEFT JOIN
    (SELECT DISTINCT
        date_of_visit,
            type_of_visit,
            weight_kg,
            Height,
            muac,
            last_muac,
            last_weight,
            caseId,
            gender,
            patient.id AS id_patient
    FROM
        openfn.odk_child_visit
    LEFT JOIN patient ON patient.patient_code = odk_child_visit.patient_code
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = patient.id
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date_of_visit) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date_of_visit) > 5
            AND date_of_visit BETWEEN @start_date AND @end_date) odk ON odk.id_patient = x.id_patient
        LEFT JOIN
    (SELECT DISTINCT
        tracking_followup.id_patient,
            date,
            mid_arm_circumference,
            weight,
            Height
    FROM
        tracking_followup
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = tracking_followup.id_patient
    WHERE
        TIMESTAMPDIFF(MONTH, dob, date) < 60
            AND TIMESTAMPDIFF(MONTH, dob, date) > 5
            AND date BETWEEN @start_date AND @end_date) f ON f.id_patient = x.id_patient
        LEFT JOIN
    (SELECT 
        patient_code, id, linked_to_id_patient
    FROM
        patient) z ON z.id = x.id_patient
        LEFT JOIN
    (SELECT 
        f1.id_patient,
            mid_arm_circumference AS hiv_last_muac,
            date AS hiv_last_muac_date
    FROM
        tracking_followup f1
    LEFT JOIN tracking_infant ON tracking_infant.id_patient = f1.id_patient
    WHERE
        date = (SELECT 
                MAX(date)
            FROM
                tracking_followup f2
            WHERE
                TIMESTAMPDIFF(MONTH, dob, date) < 60
                    AND TIMESTAMPDIFF(MONTH, dob, date) > 5
                    AND date BETWEEN @start_date AND @end_date
                    AND f1.id_patient = f2.id_patient
                    AND mid_arm_circumference IS NOT NULL
                    AND mid_arm_circumference != 0)) last_muac ON last_muac.id_patient = x.id_patient
WHERE
    x.id_patient IS NOT NULL
        AND linked_to_id_patient = 0