USE caris_db;
SET @start_date="2018-10-01";
SET @end_date="2019-03-31";
SELECT 
    first.*,
    IF(tf1.id_patient IS NOT NULL,
        'yes',
        'no') AS viral_load,
    viral_load_count,
    viral_load_indetectable,
    if(viral_load_indetectable=1,'<1000 copies',if(viral_load_count<1000,'<1000 copies', if(viral_load_count>1000,'>1000 copies',''))) as viral_load_result
    ,viral_load_date,
     timestampdiff(year, dob, viral_load_date) as viral_load_age
FROM
    (SELECT DISTINCT
        ti.id_patient,
            patient_code,
            IF(club.id_patient IS NOT NULL, 'yes', 'non') AS club,
            dob
    FROM
        tracking_infant ti
    LEFT JOIN tracking_followup tf ON tf.id_patient = ti.id_patient
    LEFT JOIN (SELECT 
        *
    FROM
        (SELECT 
        patient.id AS id_patient, date_of_visit 
    FROM
        openfn.odk_child_visit odk1
    LEFT JOIN patient ON patient.patient_code = odk1.patient_code
    WHERE
        date_of_visit BETWEEN @start_date AND @end_date)x) odk ON odk.id_patient = ti.id_patient
    LEFT JOIN (SELECT DISTINCT
        id_patient
    FROM
        session
    LEFT JOIN club_session ON club_session.id = session.id_club_session
    WHERE
        date BETWEEN @start_date AND @end_date
            AND is_present = 1) club ON club.id_patient = ti.id_patient
    LEFT JOIN patient ON patient.id = ti.id_patient
    WHERE
        (date BETWEEN @start_date AND @end_date or date_of_visit BETWEEN @start_date AND @end_date)
            AND linked_to_id_patient = 0) first
        LEFT JOIN
    (SELECT DISTINCT
        id_patient,
            viral_load_count,
            viral_load_indetectable,
            viral_load_date
    FROM
        tracking_followup tff
    WHERE
        took_viral_load_test = 1
            AND date BETWEEN @start_date AND @end_date
            AND viral_load_date = (SELECT 
                MAX(viral_load_date)
            FROM
                tracking_followup tfff
            WHERE
                tff.id_patient = tfff.id_patient
                    AND date BETWEEN @start_date AND @end_date)) tf1 ON tf1.id_patient = first.id_patient

group by first.id_patient