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

## OVC_hiv_stat

select left(patient_code,8) as hospital, count(*) as pos, sum(vbv.id_patient!=0) on_arv from((select distinct tf.id_patient from tracking_followup tf left join tracking_infant ti on ti.id_patient=tf.id_patient where timestampdiff(year, dob, @end_date)<18
and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or ti.death_date>=@start_date)
)
union
(select id_patient from (select * from (
select distinct ts.id_patient, date_blood_taken as dbt from testing_specimen ts left join tracking_infant ti on ti.id_patient=ts.id_patient where (timestampdiff(year, dob, @end_date)<18 or timestampdiff(year, date_of_birth, @end_date)<18) and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or death_date>=@start_date) and pcr_result=1 
union
select distinct tr.id_patient, blood_draw_date as dbt from testing_result tr left join tracking_infant ti on ti.id_patient=tr.id_patient where result=1 and timestampdiff(year, dob, @end_date)<18 and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or death_date>=@start_date)
)ddd
where dbt between @start_date and @end_date and dbt=(select min(date_blood_taken) from testing_specimen ts1 where ddd.id_patient=ts1.id_patient))t)
union
(
select distinct tr.id_patient from tracking_regime tr left join tracking_infant ti on ti.id_patient=tr.id_patient where timestampdiff(year, dob, @end_date)<18 and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or death_date>=@start_date) and category='regime_infant_treatment'
)
union
(
select id_patient from tracking_infant where (positive_pcr_1!='0000-00-00' or positive_pcr_2!='0000-00-00') and timestampdiff(year, dob, @end_date)<18 and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or death_date>=@start_date)
)
union
(
select cp.id_patient from club_patient cp left join club on club.id=id_club 
left join tracking_infant ti on cp.id_patient=ti.id_patient
where club_type in (2,3) and (is_abandoned=0  or abandoned_date>=@start_date) and (is_dead=0 or death_date>=@start_date)
))cdss
left join patient on patient.id=id_patient 
left join
( select distinct id_patient from tracking_followup where date<=@end_date and on_arv=1
union
select distinct id_patient from tracking_regime where start_date<=@end_date
and category='regime_infant_treatment'
)vbv on patient.id = vbv.id_patient
where linked_to_id_patient=0
group by left(patient_code,8)