USE caris_db;
SET @start_date="2019-04-01";
SET @end_date="2019-06-30";
SELECT 
    a.patient_code,
    dob,
    IF(TIMESTAMPDIFF(YEAR, dob, @start_date) >= 25,
        '25+',
        IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 25
                AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 18,
            '18-24',
            IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 18
                    AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 15,
                '15 17',
                IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 15
                        AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 10,
                    '10 15',
                    IF(TIMESTAMPDIFF(YEAR, dob, @start_date) < 10
                            AND TIMESTAMPDIFF(YEAR, dob, @start_date) >= 1,
                        '1 9',
                        ''))))) AS tranche
FROM
    (SELECT DISTINCT
        patient_code
    FROM
        (SELECT 
        patient_code
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
    LEFT JOIN (SELECT DISTINCT
        id_patient
    FROM
        tracking_regime
    WHERE
        category = 'regime_mother_treatment'
            AND (start_date BETWEEN @start_date AND @end_date
            OR end_date BETWEEN @start_date AND @end_date)) z ON z.id_patient = tracking_motherbasicinfo.id_patient
    LEFT JOIN (SELECT 
        id_patient
    FROM
        questionnaire_motherhivknowledge
    WHERE
        date BETWEEN @start_date AND @end_date UNION SELECT 
        id_patient
    FROM
        questionnaire_newmotherhivknowledge
    WHERE
        date BETWEEN @start_date AND @end_date UNION SELECT 
        id_patient
    FROM
        questionnaire_mothersurvey
    WHERE
        date BETWEEN @start_date AND @end_date) x ON x.id_patient = tracking_motherbasicinfo.id_patient
    LEFT JOIN (SELECT DISTINCT
        session.id_patient
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    WHERE
        is_present = 1
            AND club_session.date BETWEEN @start_date AND @end_date) y ON y.id_patient = tracking_motherbasicinfo.id_patient
    LEFT JOIN tracking_pregnancy on tracking_pregnancy.id_patient_mother = tracking_motherbasicinfo.id_patient
    LEFT JOIN (SELECT 
        patient.id AS id_patient
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = tracking_motherbasicinfo.id_patient
    LEFT JOIN testing_mereenfant ON CONCAT(testing_mereenfant.mother_city_code, '/', testing_mereenfant.mother_hospital_code, '/', testing_mereenfant.mother_code) = patient_code
    WHERE
        date BETWEEN @start_date AND @end_date
            AND patient_code IS NOT NULL
    GROUP BY patient_code) bb ON bb.id_patient = tracking_motherbasicinfo.id_patient
    WHERE
        patient.id IS NOT NULL
            AND (x.id_patient IS NOT NULL
            OR PTME_date BETWEEN @start_date AND @end_date
            OR tracking_pregnancy.actual_delivery_date BETWEEN @start_date AND @end_date
            or ptme_enrollment_date BETWEEN @start_date AND @end_date
            OR y.id_patient IS NOT NULL
            OR z.id_patient IS NOT NULL
            OR bb.id_patient IS NOT NULL)) c UNION SELECT DISTINCT
        health_id AS patient_code
    FROM
        openfn.odk_pregnancy_visit
    WHERE
        date_of_visit BETWEEN @start_date AND @end_date) a
        LEFT JOIN
    patient ON patient.patient_code = a.patient_code
        LEFT JOIN
    tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = patient.id
WHERE
    patient.linked_to_id_patient IS NULL
        OR patient.linked_to_id_patient = 0
GROUP BY a.patient_code