USE caris_db;
SET @start_date= "2016-10-01";
SET @end_date="2017-09-30";

select count(a.id_patient) as nbr_test, 
sum(if(timestampdiff(month,a.dob,date_blood_taken)<2,1,0)) as '<2',
sum(if(timestampdiff(month,a.dob,date_blood_taken)between 2 and 12,1,0)) as bt_2_12,
sum(if(timestampdiff(month,a.dob,date_blood_taken)>12,1,0)) as '>12',
count(b.id_patient) as nbr_on_arv,
sum(if(b.id_patient is not null,if(timestampdiff(month,a.dob,date_blood_taken)<2,1,0),0)) as on_arv_2, 
 sum(if(b.id_patient is not null,if(timestampdiff(month,a.dob,date_blood_taken)between 2 and 12,1,0),0)) as on_arv_2_12
 , sum(if(b.id_patient is not null,if(timestampdiff(month,a.dob,date_blood_taken)>12,1,0),0)) as on_arv_12,
 sum(b.id_patient is null) as total_no_arv,
 sum(if(timestampdiff(month,a.dob,date_blood_taken)<2, if(b.id_patient is null,1,0),0)) as no_arv_2, 
 sum(if(timestampdiff(month,a.dob,date_blood_taken)between 2 and 12,if(b.id_patient is null,1,0),0)) as no_arv_2_12
 , sum(if(timestampdiff(month,a.dob,date_blood_taken)>12,if(b.id_patient is null,1,0),0)) as no_arv_12,
 sum(tracking_infant.is_dead=1) as total_no_arv_dead
 ,sum(if(timestampdiff(month,a.dob,date_blood_taken)<2 and tracking_infant.is_dead=1,1,0)) as no_arv_2_dead,
 sum(if(timestampdiff(month,a.dob,date_blood_taken)between 2 and 12 and tracking_infant.is_dead=1,1,0)) as no_arv_2_12_dead,
 sum(if(timestampdiff(month,a.dob,date_blood_taken)>12 and tracking_infant.is_dead=1,1,0)) as no_arv_12_dead,
 sum(tracking_infant.is_dead=1 and tracking_infant.death_date<a.pcr_result_date) as total_no_arv_dead_before_result
 from (select id_patient, date_of_birth as dob, pcr_result_date, date_blood_taken from testing_specimen t1 
where 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)
and pcr_result = 1
group by id_patient)a
left join 
(select * from 
( select id_patient 
from tracking_followup where on_arv=1
union
select id_patient 
from tracking_regime
)x group by id_patient
)b on a.id_patient = b.id_patient
left join tracking_infant
on b.id_patient=tracking_infant.id_patient