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 b.* from
(SELECT 
  patient.id, 
  left(patient_code, 8) AS hospital_code, 
  patient_code, 
  if(
    timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1 
    and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00', 
    'under1', 
    if(
      timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1 
      and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10 
      and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00', 
      'bt_1_9', 
      if(
        timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1 
        and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
        and (
          ti.gender = 1 
          or e.gender = 1 
          or tm.infant_gender = 1
        ), 
        'm_under1', 
        if(
          timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 1 
          and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
          and (
            ti.gender = 2 
            or e.gender = 2 
            or tm.infant_gender = 2
          ), 
          'f_under1', 
          if(
            timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1 
            and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10 
            and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
            and (
              ti.gender = 1 
              or e.gender = 1 
              or tm.infant_gender = 1
            ), 
            'm_bt_1_9', 
            if(
              timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 1 
              and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 10 
              and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
              and (
                ti.gender = 2 
                or e.gender = 2 
                or tm.infant_gender = 2
              ), 
              'f_bt_1_9', 
              if(
                timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 10 
                and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 15
                and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                and (
                  ti.gender = 1 
                  or e.gender = 1 
                  or tm.infant_gender = 1
                ), 
                'm_bt_10_14', 
                if(
                  timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 10 
                  and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 15
                  and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                  and (
                    ti.gender = 2 
                    or e.gender = 2 
                    or tm.infant_gender = 2
                  ), 
                  'f_bt_10_14', 
                  if(
                    timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 15
                    and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 18 
                    and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                    and (
                      ti.gender = 1 
                      or e.gender = 1 
                      or tm.infant_gender = 1
                    ), 
                    'm_bt_15_17', 
                    if(
                      timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 15 
                      and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 18 
                      and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                      and (
                        ti.gender = 2 
                        or e.gender = 2 
                        or tm.infant_gender = 2
                      ), 
                      'f_bt_15_17', 
                      if(
                        timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 18 
                        and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 25 
                        and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                        and (
                          ti.gender = 1 
                          or e.gender = 1 
                          or tm.infant_gender = 1
                        ), 
                        'm_bt_18_24', 
                        if(
                          timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 18 
                          and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)< 25 
                          and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                          and (
                            ti.gender = 2 
                            or e.gender = 2 
                            or tm.infant_gender = 2
                          ), 
                          'f_bt_18_24', 
                          if(
                            timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 25 
                            and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                            and (
                              ti.gender = 1 
                              or e.gender = 1 
                              or tm.infant_gender = 1
                            ), 
                            'm_25', 
                            if(
                              timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date)>= 25 
                              and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                              and (
                                ti.gender = 2 
                                or e.gender = 2 
                                or tm.infant_gender = 2
                              ), 
                              'f_25', 
                              ''
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ) as tranche_age,
  if(pos.id_patient is null,'negative','positive') as status
from 
  (
    select 
      * 
    from 
      (
        select 
          testing_mereenfant.id_patient, 
          infant_dob as dob, 
          infant_gender as gender 
        from 
          testing_mereenfant 
        where 
          date between @start_date 
          and @end_date 
        union 
        select 
          testing_specimen.id_patient, 
          date_of_birth as dob, 
          gender 
        from 
          testing_specimen 
          left join tracking_infant on testing_specimen.id_patient = tracking_infant.id_patient 
        where 
          date_blood_taken between @start_date 
          and @end_date 
        union 
        select 
          id_patient, 
          dob, 
          gender 
        from 
          tracking_infant 
        where 
          (
            positive_pcr_1 between @start_date 
            and @end_date 
            or positive_pcr_2 between @start_date 
            and @end_date
          ) 
          or id_patient in (
            select 
              * 
            from 
              (
                select 
                  id_patient 
                from 
                  tracking_regime 
                where 
                  category = 'regime_infant_treatment' 
                  and start_date between @start_date 
                  and @end_date 
                union 
                select 
                  id_patient 
                from 
                  tracking_followup 
                where 
                  date between @start_date 
                  and @end_date 
                union 
                select 
                  distinct id_patient 
                from 
                  (
                    select 
                      odk.patient_code, 
                      patient.id as id_patient 
                    from 
                      openfn.odk_child_visit as odk 
                      left join patient on patient.patient_code = odk.patient_code 
                    where 
                      is_available_at_time_visit = 1 
                      and date_of_visit between @start_date 
                      and @end_date
                  ) e 
                union 
                select 
                  distinct session.id_patient 
                from 
                  session 
                where 
                  is_present = 1 
                  and id_club_session in (
                    select 
                      club_session.id 
                    from 
                      club_session 
                      left join club on club.id = club_session.id_club 
                    where 
                      date between @start_date 
                      and @end_date 
                      and club_type != 1
                  )
					union select id_patient from questionnaire_child where  date between @start_date 
                      and @end_date 
              ) nj
          )
      ) n 
    where 
      dob is not null 
      and dob != '0000-00-00' 
      and (
        gender = 1 
        or gender = 2
      ) 
      and id_patient not in (
        select 
          id_patient 
        from 
          tracking_infant 
        where 
          (
            is_dead = 1 
            and death_date < @start_date
          ) 
          or (
            is_abandoned = 1 
            and abandoned_date < @start_date
          )
      ) 
      and id_patient in (
        select 
          id 
        from 
          patient 
        where 
          linked_to_id_patient = 0
      ) 
    group by 
      id_patient
  )e 
  left join patient on patient.id = e.id_patient 
  left join tracking_infant ti on e.id_patient = ti.id_patient 
  left join testing_mereenfant tm on e.id_patient = tm.id_patient
  left join testing_specimen ts on e.id_patient = ts.id_patient
  left join questionnaire_child qc on qc.id_patient = e.id_patient
  left join ( 
							select id as id_patient from view_patient_positive
							union
							select id_patient from tracking_regime where category = 'regime_infant_treatment' and start_date between @start_date and @end_date
						 )pos on pos.id_patient = e.id_patient
group by patient.id
)a

left join

 (SELECT 
  patient.id as id_patient, 
  left(patient_code, 8) AS hospital_code, 
  patient_code, 
  if(
    timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1 
    and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00', 
    'under1', 
    if(
      timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 1 
      and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 10 
      and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00', 
      'bt_1_9', 
      if(
        timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1 
        and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
        and (
          ti.gender = 1 
          or e.gender = 1 
          or tm.infant_gender = 1
        ), 
        'm_under1', 
        if(
          timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 1 
          and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
          and (
            ti.gender = 2 
            or e.gender = 2 
            or tm.infant_gender = 2
          ), 
          'f_under1', 
          if(
            timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 1 
            and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 10 
            and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
            and (
              ti.gender = 1 
              or e.gender = 1 
              or tm.infant_gender = 1
            ), 
            'm_bt_1_9', 
            if(
              timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 1 
              and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 10 
              and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
              and (
                ti.gender = 2 
                or e.gender = 2 
                or tm.infant_gender = 2
              ), 
              'f_bt_1_9', 
              if(
                timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 10 
                and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 15
                and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                and (
                  ti.gender = 1 
                  or e.gender = 1 
                  or tm.infant_gender = 1
                ), 
                'm_bt_10_14', 
                if(
                  timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 10 
                  and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 15
                  and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                  and (
                    ti.gender = 2 
                    or e.gender = 2 
                    or tm.infant_gender = 2
                  ), 
                  'f_bt_10_14', 
                  if(
                    timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 15
                    and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 18 
                    and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                    and (
                      ti.gender = 1 
                      or e.gender = 1 
                      or tm.infant_gender = 1
                    ), 
                    'm_bt_15_17', 
                    if(
                      timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 15 
                      and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 18 
                      and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                      and (
                        ti.gender = 2 
                        or e.gender = 2 
                        or tm.infant_gender = 2
                      ), 
                      'f_bt_15_17', 
                      if(
                        timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 18 
                        and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 25 
                        and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                        and (
                          ti.gender = 1 
                          or e.gender = 1 
                          or tm.infant_gender = 1
                        ), 
                        'm_bt_18_24', 
                        if(
                          timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 18 
                          and timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)< 25 
                          and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                          and (
                            ti.gender = 2 
                            or e.gender = 2 
                            or tm.infant_gender = 2
                          ), 
                          'f_bt_18_24', 
                          if(
                            timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 25 
                            and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                            and (
                              ti.gender = 1 
                              or e.gender = 1 
                              or tm.infant_gender = 1
                            ), 
                            'm_25', 
                            if(
                              timestampdiff(year, COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob), @start_date_curr)>= 25 
                              and COALESCE(if(tm.infant_dob='0000-00-00',null,tm.infant_dob),if(ts.date_of_birth='0000-00-00',null,ts.date_of_birth),ti.dob) != '0000-00-00' 
                              and (
                                ti.gender = 2 
                                or e.gender = 2 
                                or tm.infant_gender = 2
                              ), 
                              'f_25', 
                              ''
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ) as tranche_age,
  if(pos.id_patient is null,'negative','positive') as status
from 
  (
    select 
      * 
    from 
      (
        select 
          testing_mereenfant.id_patient, 
          infant_dob as dob, 
          infant_gender as gender 
        from 
          testing_mereenfant 
        where 
          date between @start_date_curr 
          and @end_date_curr 
        union 
        select 
          testing_specimen.id_patient, 
          date_of_birth as dob, 
          gender 
        from 
          testing_specimen 
          left join tracking_infant on testing_specimen.id_patient = tracking_infant.id_patient 
        where 
          date_blood_taken between @start_date_curr 
          and @end_date_curr 
        union 
        select 
          id_patient, 
          dob, 
          gender 
        from 
          tracking_infant 
        where 
          (
            positive_pcr_1 between @start_date_curr 
            and @end_date_curr 
            or positive_pcr_2 between @start_date_curr 
            and @end_date_curr
          ) 
          or id_patient in (
            select 
              * 
            from 
              (
                select 
                  id_patient 
                from 
                  tracking_regime 
                where 
                  category = 'regime_infant_treatment' 
                  and start_date between @start_date_curr 
                  and @end_date_curr 
                union 
                select 
                  id_patient 
                from 
                  tracking_followup 
                where 
                  date between @start_date_curr 
                  and @end_date_curr 
                union 
                select 
                  distinct id_patient 
                from 
                  (
                    select 
                      odk.patient_code, 
                      patient.id as id_patient 
                    from 
                      openfn.odk_child_visit as odk 
                      left join patient on patient.patient_code = odk.patient_code 
                    where 
                      is_available_at_time_visit = 1 
                      and date_of_visit between @start_date_curr 
                      and @end_date_curr
                  ) e 
                union 
                select 
                  distinct session.id_patient 
                from 
                  session 
                where 
                  is_present = 1 
                  and id_club_session in (
                    select 
                      club_session.id 
                    from 
                      club_session 
                      left join club on club.id = club_session.id_club 
                    where 
                      date between @start_date_curr 
                      and @end_date_curr 
                      and club_type != 1
                  )
              ) nj
          )
      ) n 
    where 
      dob is not null 
      and dob != '0000-00-00' 
      and (
        gender = 1 
        or gender = 2
      ) 
      and id_patient not in (
        select 
          id_patient 
        from 
          tracking_infant 
        where 
          (
            is_dead = 1 
            and death_date < @start_date_curr
          ) 
          or (
            is_abandoned = 1 
            and abandoned_date < @start_date_curr
          )
      ) 
      and id_patient in (
        select 
          id 
        from 
          patient 
        where 
          linked_to_id_patient = 0
      ) 
    group by 
      id_patient
  )e 
  left join patient on patient.id = e.id_patient 
  left join tracking_infant ti on e.id_patient = ti.id_patient 
  left join testing_mereenfant tm on e.id_patient = tm.id_patient
  left join testing_specimen ts on e.id_patient = ts.id_patient
  left join ( 
							select id as id_patient from view_patient_positive
							union
							select id_patient from tracking_regime where category = 'regime_infant_treatment' and start_date between @start_date_curr and @end_date_curr
						 )pos on pos.id_patient = e.id_patient
group by patient.id
)b on a.id=b.id_patient
where id_patient is not null