use caris_db;
set @start_date = '2018-05-01';
set @end_date = '2018-05-31';
select patient_code, if(tracking_infant.first_name is not null,tracking_infant.first_name, tracking_motherbasicinfo.first_name) as first_name,
if(tracking_infant.last_name is not null,tracking_infant.last_name, tracking_motherbasicinfo.last_name) as last_name
,if(tracking_infant.id_patient is not null, if(tracking_infant.gender=1,'male',if(tracking_infant.gender=2,'female','')),'female') as gender
,if(tracking_infant.dob is not null,tracking_infant.dob, tracking_motherbasicinfo.dob) as dob
,left(patient_code,8) as hospital_code
,lookup_hospital.name as hospital_name
,lookup_hospital.office as office
,club.name as club_name
,lookup_club_type.name as club_type
, lookup_club_topic.name_fr as topic
, club_session.date as training_date
, if(s.id_patient is not null, 'yes','no') as on_arv
from club_session 
left join session on session.id_club_session=club_session.id
left join club_patient on club_patient.id_patient=session.id_patient
left join club on club.id=club_session.id_club
left join patient on patient.id=session.id_patient
left join tracking_infant on tracking_infant.id_patient=session.id_patient
left join tracking_motherbasicinfo on tracking_motherbasicinfo.id_patient=session.id_patient
left join lookup_club_type on lookup_club_type.id=club_type
left join lookup_club_topic on lookup_club_topic.id=topic
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 union select distinct id_patient from tracking_followup where on_arv=1)s on s.id_patient=patient.id
where club_session.date between @start_date and @end_date and is_present=1 and linked_to_id_patient=0