HEI_POS

USE caris_db;
SET @start_date='2018-01-01';
SET @end_date='2018-03-31';

select left(patient_code,8) as hospital_code, sum(if(timestampdiff(month,date_of_birth,date_blood_taken)<2,1,0)) as within_2,
sum(if(timestampdiff(month,date_of_birth,date_blood_taken) between 2 and 12,1,0)) as bt_2_12,
sum(if(timestampdiff(month,date_of_birth,date_blood_taken)<2,if(y.id_patient is not null,1,0),0)) as within_2_on_arv,
sum(if(timestampdiff(month,date_of_birth,date_blood_taken) between 2 and 12,if(y.id_patient is not null,1,0),0)) as bt_2_12_on_arv,
sum(if(y.id_patient is null,if(tracking_infant.is_dead=1 or tracking_infant.is_abandoned,1,0),0)) as not_on_arv_dead_abandonned
from testing_specimen t1
left join
(select * from(
select id_patient from tracking_regime where category='regime_infant_treatment'
union
select id_patient from tracking_followup where on_arv=1
)x)y on y.id_patient =t1.id_patient
left join patient on patient.id=t1.id_patient
left join tracking_infant on tracking_infant.id_patient=patient.id
where pcr_result=1 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) and timestampdiff(month,date_of_birth,date_blood_taken) <= 12
and linked_to_id_patient=0
group by left(patient_code,8)