USE caris_db;
SET
  @start_date = "2019-01-01";
SET
  @end_date = "2019-03-31";
SELECT 
    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,
    IF(regime.id_patient IS NOT NULL,
        'yes',
        'non') AS on_arv,
    office
FROM
    (SELECT 
        id_patient, MIN(date) AS date
    FROM
        (SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        questionnaire_motherhivknowledge
    WHERE
        date IS NOT NULL
            AND date != '0000-00-00' UNION SELECT 
        id_patient, date
    FROM
        questionnaire_mothersurvey
    WHERE
        date IS NOT NULL
            AND date != '0000-00-00' UNION SELECT 
        id_patient, date
    FROM
        questionnaire_newmotherhivknowledge
    WHERE
        date IS NOT NULL
            AND date != '0000-00-00') quest
    LEFT JOIN patient ON patient.id = quest.id_patient
    WHERE
        patient.created_at >= @start_date) ques UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        patient.id AS id_patient, date_of_visit AS date
    FROM
        patient
    LEFT JOIN openfn.odk_pregnancy_visit ON patient.patient_code = health_id
    WHERE
        date_of_visit IS NOT NULL
            AND date_of_visit != '0000-00-00'
            AND patient.created_at >= @start_date) odk UNION SELECT 
        id_patient, date
    FROM
        (SELECT 
        id_patient, date
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    LEFT JOIN club ON club.id = id_club
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        is_present = 1 AND club_type = 1
            AND patient.created_at >= @start_date) club UNION SELECT 
        mereenfant.id AS id_patient, date
    FROM
        (SELECT 
        patient.id, date
    FROM
        testing_mereenfant
    LEFT JOIN patient ON patient.patient_code = CONCAT(city_code, '/', hospital_code, '/', mother_code)
    WHERE
        patient.id IS NOT NULL
            AND patient.created_at >= @start_date) mereenfant) last
    GROUP BY id_patient) lastone
        LEFT JOIN
    patient ON patient.id = lastone.id_patient
        LEFT JOIN
    tracking_motherbasicinfo ON tracking_motherbasicinfo.id_patient = lastone.id_patient
        LEFT JOIN
    lookup_hospital ON CONCAT(lookup_hospital.city_code,
            '/',
            lookup_hospital.hospital_code) = LEFT(patient_code, 8)
        LEFT JOIN
    (SELECT 
       distinct id_patient
    FROM
        tracking_regime
    WHERE
        category = 'regime_mother_treatment') regime ON regime.id_patient = lastone.id_patient
WHERE
    date BETWEEN @start_date AND @end_date
        AND patient.created_at >= @start_date
        AND lastone.id_patient IS NOT NULL
        AND tracking_motherbasicinfo.id_patient IS NOT NULL