USE caris_db;
SET @start_date= "2017-01-01";
select distinct id_patient from testing_result where result=1
union
select id_patient from testing_specimen where pcr_result=1
union
select id_patient from tracking_infant where timestampdiff(year,dob,@start_date)<=18 and timestampdiff(YEAR,dob,@start_date)>=0
and id_patient in
(select * from
(select id_patient from tracking_followup
union
select id_patient from tracking_regime
union
select id_patient from club_patient
union
select id_patient from session
)f
)
and id_patient in (select id from patient where linked_to_id_patient=0)
and 
(is_dead=0 or year(death_date)=2017)
and
(is_abandoned=0 or year(abandoned_date)=2017)