OVC_SERV children detailed

USE caris_db;
SET
  @start_date = "2018-07-01";
SET
  @end_date = "2018-09-30";
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
                  )
              ) 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 (
                            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