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 
        tracking_motherbasicinfo.id_patient
    FROM
        testing_mereenfant
    LEFT JOIN patient ON patient.patient_code = CONCAT(mother_city_code, '/', mother_hospital_code, '/', mother_code)
    LEFT JOIN tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = patient.id
    WHERE
        patient.id IS NOT NULL and linked_to_id_patient = 0
            AND date BETWEEN @start_date AND @end_date) abc ON abc.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 tracking_pregnancy.ptme_enrollment_date BETWEEN @start_date AND @end_date
            OR y.id_patient IS NOT NULL
            OR z.id_patient IS NOT NULL
            OR abc.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 linked_to_id_patient = 0 or linked_to_id_patient is null
GROUP BY a.patient_code