USE caris_db;
SET @start_date= "2017-01-01";
SET @end_date="2018-01-01";
select x.id_patient, patient_code, dob, last_followup_date, timestampdiff(month,dob, last_followup_date) as age_en_mois, took_viral_load_test, viral_load_count, charge_virale_indetectable,
if(is_dead=0, "no","yes") as mort,
tracking_infant.death_date, if(is_abandoned=0, "no","yes") as abandon,
if(z.id_patient is not NULL, "yes","non") as on_arv,
tracking_regime.start_date as initiation_date
 from(
select id_patient from testing_specimen where pcr_result=1
union
select id_patient from tracking_regime where category='regime_infant_treatment'
union
select id_patient from tracking_followup
union
select id_patient from testing_result where result=1
)x
left join 
(
	select id_patient, date as last_followup_date from tracking_followup f where date=(select max(date) from tracking_followup f1 where f.id_patient=f1.id_patient and date between @start_date and now())
)y on y.id_patient=x.id_patient
left join tracking_infant on tracking_infant.id_patient=x.id_patient
left join patient on patient.id=x.id_patient
left join 
(select id_patient, took_viral_load_test, viral_load_count, if(viral_load_indetectable=1, "yes","no") as charge_virale_indetectable from tracking_followup f1
where date=(select max(date) from tracking_followup f3 where f1.id_patient=f3.id_patient and took_viral_load_test=1)) r
 on r.id_patient = x.id_patient
left join
(
   select id_patient from tracking_followup where on_arv=1
   union
   select id_patient from tracking_regime
)z on z.id_patient=x.id_patient
left join tracking_regime on tracking_regime.id_patient=x.id_patient
where linked_to_id_patient=0
and timestampdiff(year,dob,last_followup_date)<18 #and timestampdiff(year,dob,last_followup_date)>=1 
and  last_followup_date between @start_date and now()
group by id_patient