USE caris_db;
SET @start_date="2018-04-01";
SET @end_date="2018-06-31";
# this code will only work for patient after 2014
select patient_code, hospital, date_of_birth, gender, start_date as arv_start, lab, date_blood_taken, date_caris_received_sample, date_lab_received_sample, result, office,
if(timestampdiff(day,date_of_birth,date_blood_taken)<=61,if(timestampdiff(day,date_of_birth,date_blood_taken)>=0,'0_2',''),if(timestampdiff(day,date_of_birth,date_blood_taken)<=365,if(timestampdiff(day,date_of_birth,date_blood_taken)>61,'2_12',''),'')) as tranche_age
,if(arv is not null,'yes','') as on_arv
,if(pcr_result=1,if(patient_info is not null,if(is_abandoned=0 and is_dead=0,'no','yes'),''),'') as pos_dead_abandon
,death_date
,abandoned_date
,pcr_result_date
from(
select patient_code, date_of_birth, tracking_infant.id_patient as patient_info, office, date_blood_taken, pcr_result,arv.id_patient as arv, is_dead, is_abandoned, death_date, abandoned_date, pcr_result_date, date_caris_received_sample, date_lab_received_sample, 
lookup_testing_specimen_result.name as result, lookup_hospital.name as hospital, lookup_testing_gender.name as gender, tracking_regime.start_date, lookup_lab.name as lab from(
	select * from testing_specimen t1 where timestampdiff(day,date_of_birth,date_blood_taken)<=365 and date_blood_taken between @start_date and @end_date 
    and date_blood_taken=(select min(date_blood_taken) from testing_specimen t2 where t1.id_patient=t2.id_patient)
)test_1
left join patient pa on pa.id=test_1.id_patient
left join
(select * from(
	select id_patient from tracking_followup where on_arv=1
    union
    select id_patient from tracking_regime where category='regime_infant_treatment'
    )o
)arv on arv.id_patient=test_1.id_patient
left join tracking_infant on tracking_infant.id_patient=test_1.id_patient
left join testing_mereenfant on testing_mereenfant.id_patient=test_1.id_patient
left join lookup_testing_specimen_result on pcr_result=lookup_testing_specimen_result.id
left join lookup_hospital on concat(lookup_hospital.city_code,'/',lookup_hospital.hospital_code)=left(patient_code,8)
left join tracking_regime on tracking_regime.id_patient=arv.id_patient
left join lookup_testing_gender on lookup_testing_gender.id=infant_gender
left join lookup_lab on lookup_hospital.id_lab=lookup_lab.id
where timestampdiff(day,test_1.date_of_birth,test_1.date_blood_taken)<=365 and test_1.date_blood_taken between @start_date and @end_date and linked_to_id_patient=0
group by test_1.id_patient
)final