USE caris_db;
SET @start_date='2016-10-01';
SET @end_date= now();
select distinct mother_code, concat(tracking_motherbasicinfo.first_name,' ',tracking_motherbasicinfo.last_name) as mother_name,
patient_code, lookup_hospital.name as hospital, lookup_lab.name as lab,date_of_birth, date_blood_taken, 
lookup_testing_which_pcr.name as which_pcr, lookup_testing_specimen_result.name as pcr_result, pcr_result_date from testing_mereenfant
left join testing_specimen on testing_specimen.id_patient = testing_mereenfant.id_patient
left join patient on patient.id = testing_specimen.id_patient
left join lookup_hospital on concat(lookup_hospital.city_code,'/',lookup_hospital.hospital_code) = concat(patient.city_code,'/',patient.hospital_code)
left join lookup_lab on lookup_lab.id = lookup_hospital.id_lab
left join lookup_testing_which_pcr on lookup_testing_which_pcr.id = which_pcr
left join lookup_testing_specimen_result on lookup_testing_specimen_result.id = pcr_result
left join tracking_children on testing_specimen.id_patient=id_patient_child
left join tracking_motherbasicinfo on tracking_motherbasicinfo.id_patient=id_patient_mother
where date_blood_taken >= @start_date and date_blood_taken <= @end_date
and pcr_result_date <> '0000-00-00'
and linked_to_id_patient = 0