USE caris_db;
SET @start_date='2016-10-01';
SET @end_date='2017-09-30';
select left(patient_code,8) as hospital, sum(which_pcr=5) as pcr_n, sum(which_pcr=1)as pcr_1, sum(which_pcr=2) as pcr2, count(detecte) as detecte, count(on_arv) as on_arv, count(confirmation) as confirmation
from testing_specimen 
left join
patient on patient.id=id_patient
left join
(select distinct id_patient as detecte from testing_specimen where pcr_result=1)a on detecte=id_patient
left join
(select * from(
select distinct id_patient as on_arv from tracking_followup where on_arv=1
union
select distinct id_patient as on_arv from tracking_regime where category='regime_infant_treatment'
)a where on_arv in (select id_patient from tracking_infant where is_dead=0 or death_date>=death_date)
and on_arv in(select distinct id_patient as detecte from testing_specimen where pcr_result=1)
)s on on_arv=id_patient
left join(
select id_patient as confirmation from testing_specimen t1 where pcr_result=1 and id_patient in 
(select id_patient from testing_specimen t2 where pcr_result=1 and t1.date_blood_taken<t2.date_blood_taken)
)za on confirmation=id_patient
where date_blood_taken between @start_date and @end_date and linked_to_id_patient=0
group by hospital