ovc_serv 2017 commcare included

USE caris_db;
SET @start_date= "2017-10-01";
SET @end_date="2018-03-31";
SELECT left(patient_code,8) AS hospital_code, count(*) as total
,sum(if(dob between (@start_date - interval 364 day) and @end_date
            and dob != '0000-00-00',1,0)) as under1
,
sum(if(dob between (@start_date - interval 9 year + interval 1 day) and @end_date - interval 1 year
            and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>=1
            ,1,0)) as bt_1_9,
sum(if(dob between (@start_date - interval 364 day) and @end_date
            and dob != '0000-00-00'
            and gender = 1,1,0)) as m_under1,
sum(if(dob between (@start_date - interval 364 day) and @end_date
            and dob != '0000-00-00'
            and gender = 2,1,0)) as f_under1,
sum(if(dob between (@start_date - interval 9 year + interval 1 day) and @end_date - interval 1 year
            and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>=1
            and gender = 1,1,0)) as m_bt_1_9,
sum(if(dob between (@start_date - interval 9 year + interval 1 day) and @end_date - interval 1 year
            and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>=1
            and gender = 2,1,0)) as f_bt_1_9,
sum(if(dob between (@start_date - interval 14 year + interval 1 day) and @end_date - interval 10 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 10
              and gender = 1,1,0)) as m_bt_10_14,
sum(if(dob between (@start_date - interval 14 year + interval 1 day) and @end_date - interval 10 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 10
              and gender = 2,1,0)) as f_bt_10_14,
sum(if(dob between (@start_date - interval 17 year + interval 1 day) and @end_date - interval 15 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 15
              and gender = 1,1,0)) as m_bt_15_17,
sum(if(dob between (@start_date - interval 17 year + interval 1 day) and @end_date - interval 15 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 15
              and gender = 2,1,0)) as f_bt_15_17,
sum(if(dob between (@start_date - interval 24 year + interval 1 day) and @end_date - interval 18 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 18
              and gender = 1,1,0)) as m_bt_18_24,
sum(if(dob between (@start_date - interval 24 year + interval 1 day) and @end_date - interval 18 year
              and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 18
              and gender = 2,1,0)) as f_bt_18_24,
sum(if(dob <= @end_date - interval 25 year and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 25
            and gender = 1,1,0)) as m_25,
sum(if(dob <= @end_date - interval 25 year and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 25
            and gender = 2,1,0)) as f_25
 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=id_patient
 group by hospital_code