USE caris_db;
SET @start_date="2016-07-01";
SET @end_date="2016-09-31";
# this code will only work for patient after 2014
select left(patient_code,8) as hospital_code, sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=61,if(timestampdiff(day,date_of_birth,date_blood_taken)>=0,1,0),0)) as test_0_2,
sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=365,if(timestampdiff(day,date_of_birth,date_blood_taken)>61,1,0),0)) as test_2_12
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=61,if(timestampdiff(day,date_of_birth,date_blood_taken)>=0,if(pcr_result=1,1,0),0),0)) as pos_0_2
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=365,if(timestampdiff(day,date_of_birth,date_blood_taken)>61,if(pcr_result=1,1,0),0),0)) as pos_2_12
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=61,if(timestampdiff(day,date_of_birth,date_blood_taken)>=0,if(pcr_result=1,if(arv is not null,1,0),0),0),0)) as arv_0_2
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=365,if(timestampdiff(day,date_of_birth,date_blood_taken)>61,if(pcr_result=1,if(arv is not null,1,0),0),0),0)) as arv_2_12
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=61,if(timestampdiff(day,date_of_birth,date_blood_taken)>=0,if(pcr_result=1,if(arv is not null,if(is_abandoned=0 and is_dead=0,0,1),0),0),0),0)) as dead_abandon_0_2
,sum(if(timestampdiff(day,date_of_birth,date_blood_taken)<=365,if(timestampdiff(day,date_of_birth,date_blood_taken)>61,if(pcr_result=1,if(arv is not null,if(is_abandoned=0 and is_dead=0,0,1),0),0),0),0)) as dead_abandon_2_12
from(
select patient_code, date_of_birth, date_blood_taken, pcr_result,arv.id_patient as arv, is_dead, is_abandoned 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
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
GROUP BY hospital_code