USE caris_db;
SET #start_date for Q1
  @start_date = "2019-04-01";
SET #end_date for Q1
  @end_date = "2019-06-30";
SET #start_date for Q2
	@start_date_curr = "2019-07-01";
SET #end_date for Q2
	@end_date_curr = "2019-09-30";
select patient_code, id, mereenfant_1, mereenfant_2, specimen_1, specimen_2, followup_1, followup_2, arv_1, arv_2, quest_1, quest_2, odk_1, odk_2, club_1, club_2
from patient p
left join
(select id_patient, mereenfant_1, mereenfant_2  from testing_mereenfant tm
left join (select id_patient as mereenfant_1 from testing_mereenfant where (date between @start_date and @end_date))x1 on tm.id_patient = mereenfant_1
left join (select id_patient as mereenfant_2 from testing_mereenfant where (date between @start_date_curr and @end_date_curr))x2 on tm.id_patient = mereenfant_2
)a on p.id = a.id_patient
left join
(
select id_patient, specimen_1, specimen_2 from testing_specimen ts
left join (select distinct id_patient as specimen_1 from testing_specimen where (date_blood_taken between @start_date and @end_date))y1 on ts.id_patient = specimen_1
left join (select distinct id_patient as specimen_2 from testing_specimen where (date_blood_taken between @start_date_curr and @end_date_curr))y2 on ts.id_patient = specimen_2

)b on p.id = b.id_patient
left join
(
select distinct id_patient, followup_1, followup_2 from tracking_followup tf
left join (select distinct id_patient as followup_1 from tracking_followup where (date between @start_date and @end_date))z1 on tf.id_patient = followup_1
left join (select distinct id_patient as followup_2 from tracking_followup where (date between @start_date_curr and @end_date_curr))z2 on tf.id_patient = followup_2

)c on p.id = c.id_patient
left join
(
select distinct id_patient, arv_1, arv_2 from tracking_regime tr
left join (select distinct id_patient as arv_1 from tracking_regime where category = 'regime_infant_treatment' and (start_date between @start_date and @end_date or end_date between @start_date and @end_date))zz1 on tr.id_patient = arv_1
left join (select distinct id_patient as arv_2 from tracking_regime where category = 'regime_infant_treatment' and (start_date between @start_date_curr and @end_date_curr or end_date between @start_date_curr and @end_date_curr))zz2 on tr.id_patient = arv_2
where category = 'regime_infant_treatment')d on p.id = d.id_patient
left join
(
select distinct id_patient, quest_1, quest_2 from questionnaire_child qc
left join (select distinct id_patient as quest_1 from questionnaire_child where (date between @start_date and @end_date))zq1 on qc.id_patient = quest_1
left join (select distinct id_patient as quest_2 from questionnaire_child where (date between @start_date_curr and @end_date_curr))zq2 on qc.id_patient = quest_2
)e on p.id = e.id_patient
left join
(
select distinct patient.id as id_patient, odk_1, odk_2 from openfn.odk_child_visit odk
left join patient on patient.patient_code = odk.patient_code
left join (select distinct patient.id as odk_1 from openfn.odk_child_visit odk left join patient on patient.patient_code = odk.patient_code where date_of_visit between @start_date and @end_date AND is_available_at_time_visit = 1
            )za1 on patient.id = odk_1
left join (select distinct patient.id as odk_2 from openfn.odk_child_visit odk left join patient on patient.patient_code = odk.patient_code where date_of_visit between @start_date_curr and @end_date_curr and is_available_at_time_visit = 1
            )za2 on patient.id = odk_2
where patient.id is not null AND linked_to_id_patient = 0
)f on p.id = f.id_patient
left join
(
select distinct id_patient, club_1, club_2 from session s
left join club_session on club_session.id = id_club_session
left join club on club_session.id_club=club.id 
left join
(SELECT DISTINCT
        id_patient AS club_1
    FROM
        session
    LEFT JOIN club_session ON club_session.id = id_club_session
    LEFT JOIN club ON club_session.id_club = club.id
    WHERE
        is_present = 1
            AND date BETWEEN @start_date AND @end_date
            AND club_type != 1)zf1 on club_1 = s.id_patient
            left join
(SELECT DISTINCT
        id_patient AS club_2
    FROM
        session
    LEFT JOIN club_session ON club_session.id = id_club_session
    LEFT JOIN club ON club_session.id_club = club.id
    WHERE
        is_present = 1
            AND date BETWEEN @start_date_curr AND @end_date_curr
            AND club_type != 1)zf2 on club_2 = s.id_patient
where #date between @start_date and @end_date_curr and 
club_type !=1 and is_present =1
)g on p.id = g.id_patient

where (mereenfant_1 is not null 
or 
specimen_1 is not null or followup_1 is not null or arv_1 is not null
or quest_1 is not null or odk_1 is not null or club_1 is not null
) 
and (mereenfant_2 is not null
or 
specimen_2 is not null or followup_2 is not null or arv_2 is not null
or quest_2 is not null or odk_2 is not null or club_2
)

group by p.id