Pasted

USE caris_db;
SET @start_date="2017-04-01";
SET @end_date= "2017-09-30";
 
select patient.patient_code, last_club_session
, last_followup
#, last_odk
, last_arv_ini
, e.date as date_mereenfant
,f.date as last_specimen
, GREATEST(
     COALESCE(last_club_session,0), 
     COALESCE(last_followup, 0),
     COALESCE(last_arv_ini, 0),
     COALESCE(e.date, 0),
     COALESCE(f.date, 0)
     #COALESCE(last_odk, 0)
     ) as last_contact
, if(coalesce(infant_gender, gender)=1,'male',if(coalesce(infant_gender, gender)=2,'female', if(coalesce(infant_gender, gender)=3,'unknown',''))) as gender
, coalesce(infant_dob, dob, date_of_birth) as dob
, timestampdiff(year, coalesce(infant_dob, dob, date_of_birth), @end_date) as age
, if(is_dead=1, 'dead',if(timestampdiff(month,last_club_session, @end_date)<=6,'active',
                        if(timestampdiff(month,last_followup, @end_date)<=6,'active',
                            if(timestampdiff(month,last_followup, @end_date)<=6,'active',
                                if(timestampdiff(month,last_arv_ini, @end_date)<=6,'active',
                                    if(timestampdiff(month,e.date, @end_date)<=6,'active',
                                        if(timestampdiff(month,f.date, @end_date)<=6,'active','inactive'))))))) as statut
, is_dead 
, if(h.id_patient is not null, "positive","neg") as statut_vih
from patient 
left join (
    select id_patient, date as last_club_session from session
        left join 
            ( select id, date, id_club from club_session cs1 where date=(select max(date) from club_session cs2 where date between @start_date and @end_date and cs2.id_club=cs1.id_club)
            )a1 on a1.id=id_club_session
        left join club on club.id=id_club
    where is_present=1 and club_type!=1
)a on patient.id=a.id_patient
left join (
     select id_patient, date as last_followup from tracking_followup tf1 where date =(select max(date) from tracking_followup tf2 where date between @start_date and @end_date and tf1.id_patient=tf2.id_patient)
)b on patient.id=b.id_patient
#left join (
#   select patient_code, date_of_visit as last_odk from openfn.odk_child_visit o1
#    where timestampdiff(month,date_of_visit, @end_date)<=6 and date_of_visit=(select max(date_of_visit) from openfn.odk_child_visit o2 where date_of_visit between @start_date and @end_date and o1.patient_code=o2.patient_code )
#)c on c.patient_code=patient.patient_code
left join (
    select id_patient, start_date as last_arv_ini from tracking_regime tr1 where category= 'regime_infant_treatment' and start_date=(select max(start_date) from tracking_regime tr2 where start_date between @start_date and @end_date and tr1.id_patient=tr2.id_patient)
)d on d.id_patient=patient.id
left join (select id_patient, date, infant_dob, infant_gender from testing_mereenfant where date between @start_date and @end_date
)e on e.id_patient=patient.id
left join (
select * from(
    select id_patient, date_blood_taken as date, which_pcr from testing_specimen 
    union
    select id_patient, blood_draw_date as date, which_pcr from testing_result
    )f1 where date=(select max(date) from (select * from (select id_patient, date_blood_taken as date, which_pcr from testing_specimen where date_blood_taken between @start_date and @end_date
    union
    select id_patient, blood_draw_date as date, which_pcr from testing_result where blood_draw_date between @start_date and @end_date)f2 
    )f4 where f1.id_patient=f4.id_patient
)
)f on f.id_patient=patient.id
left join tracking_infant ti on ti.id_patient=patient.id
left join (
             select id_patient, date_of_birth from testing_specimen
             group by id_patient
) g on g.id_patient= patient.id
left join( select id as id_patient from view_patient_positive
union 
select id_patient from tracking_regime where category='regime_infant_treatment'
)h on h.id_patient=patient.id
where 
 (a.id_patient is not null 
 or b.id_patient is not null 
 #or c.patient_code is not null
 or d.id_patient is not null 
 or e.id_patient is not null
 or f.id_patient is not null
 )
and linked_to_id_patient=0 
group by patient.id