Pasted

USE caris_db;
SET  @start_date = '2018-10-01';
SET @end_date = '2019-06-30';
SELECT 
    lookup_hospital.name AS club_hospital,
    patient_code,
    lookup_club_type.name AS club_type,
    club.name,
    IF(tm.id_patient IS NOT NULL,
        'female',
        lookup_gender.name) AS gender,
    COALESCE(tracking_infant.dob, tm.dob) AS dob,
    date AS date_session,
    COALESCE(tracking_infant.id_patient,
            tm.id_patient) AS id_patient,
    COALESCE(tracking_infant.first_name,
            tm.first_name) AS first_name,
    COALESCE(tracking_infant.last_name, tm.last_name) AS last_name,
    CONCAT(MONTH(date), '/', YEAR(date)) AS month_session
FROM
    session
        LEFT JOIN
    club_session ON club_session.id = session.id_club_session
        LEFT JOIN
    club ON club.id = club_session.id_club
        LEFT JOIN
    patient ON patient.id = session.id_patient
        LEFT JOIN
    lookup_club_type ON lookup_club_type.id = club_type
        LEFT JOIN
    lookup_hospital ON lookup_hospital.id = id_hospital
        LEFT JOIN
    tracking_infant ON tracking_infant.id_patient = session.id_patient
        LEFT JOIN
    (SELECT 
        id_patient, dob, first_name, last_name
    FROM
        tracking_motherbasicinfo
    LEFT JOIN patient ON patient.id = id_patient
    WHERE
        patient.id IS NOT NULL) tm ON tm.id_patient = session.id_patient
        LEFT JOIN
    lookup_gender ON lookup_gender.id = gender
WHERE
    date BETWEEN @start_date AND @end_date
        AND is_present = 1
        AND linked_to_id_patient = 0