OVC_SERV with all disagg

USE caris_db;
SET @dob_diff_date= "2015-01-01";
SET @start_date= "2014-10-01";
SET @end_date="2015-09-30";

select lookup_hospital.hospital_code, total, male, female,
under_1, f_under_1, m_under_1, btwn_1_and_4, f_btwn_1_and_4, m_btwn_1_and_4, btwn_5_and_9, f_btwn_5_and_9, m_btwn_5_and_9, btwn_10_and_14, f_btwn_10_and_14, m_btwn_10_and_14 , btwn_15_and_17, f_btwn_15_and_17, m_btwn_15_and_17 , 18plus, f_18plus, m_18plus
 from
(SELECT
        CONCAT(city_code, '/', hospital_code) AS hospital_code,
            hospital_code AS hcode_nocity
    FROM
        lookup_hospital) lookup_hospital
        LEFT JOIN

(
select CONCAT(city_code, '/', hospital_code) AS hospital_code , count(*) as "total",
#gender, dob ,
#datediff(@dob_diff_date,dob) /365 as age,
 SUM(CASE
        WHEN
            gender = 2
        THEN
            1
        ELSE 0
    END) AS male,
 SUM(CASE
        WHEN
            gender = 1
        THEN
            1
        ELSE 0
    END) AS female,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=0
            AND  datediff(@dob_diff_date,dob) /365 <1
        THEN
            1
        ELSE 0
    END) AS under_1,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=0
            AND  datediff(@dob_diff_date,dob) /365 <1
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_under_1,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=0
            AND  datediff(@dob_diff_date,dob) /365 <1
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_under_1,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=1
            AND  datediff(@dob_diff_date,dob) /365 <5
        THEN
            1
        ELSE 0
    END) AS btwn_1_and_4,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=1
            AND  datediff(@dob_diff_date,dob) /365 <5
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_btwn_1_and_4,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=1
            AND  datediff(@dob_diff_date,dob) /365 <5
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_btwn_1_and_4,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=5
            AND  datediff(@dob_diff_date,dob) /365 <10
        THEN
            1
        ELSE 0
    END) AS btwn_5_and_9,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=5
            AND  datediff(@dob_diff_date,dob) /365 <10
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_btwn_5_and_9,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=5
            AND  datediff(@dob_diff_date,dob) /365 <10
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_btwn_5_and_9,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=10
            AND  datediff(@dob_diff_date,dob) /365 <15
        THEN
            1
        ELSE 0
    END) AS btwn_10_and_14,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=10
            AND  datediff(@dob_diff_date,dob) /365 <15
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_btwn_10_and_14,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=10
            AND  datediff(@dob_diff_date,dob) /365 <15
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_btwn_10_and_14,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=15
            AND  datediff(@dob_diff_date,dob) /365 <18
        THEN
            1
        ELSE 0
    END) AS btwn_15_and_17,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=15
            AND  datediff(@dob_diff_date,dob) /365 <18
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_btwn_15_and_17,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=15
            AND  datediff(@dob_diff_date,dob) /365 <18
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_btwn_15_and_17,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=18
        THEN
            1
        ELSE 0
    END) AS 18plus,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=18
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_18plus,
    SUM(CASE
        WHEN
            datediff(@dob_diff_date,dob) /365 >=18
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_18plus
from
/*
(
select id_patient, infant_dob as dob , infant_gender as gender
from testing_mereenfant
where date >=@start_date and date<@end_date
) i , patient
*/
(
(
select id_patient, infant_dob as dob , infant_gender as gender
from testing_mereenfant
where date >=@start_date and date<@end_date )

union
(
select id_patient, dob ,  gender
from patient LEFT JOIN tracking_infant
ON tracking_infant.id_patient = patient.id
  and
  (
    (
      (    kids_club_program =1
        or id_patient in (select id from tracking_followup where date>=@start_date AND  date<@end_date )
      )
      AND id_patient not in (select id_patient from testing_mereenfant where date >=@start_date and date<@end_date )
    )
    OR
    (
        id_patient in (
            select id from patient where

                (
                created_at >=@start_date and created_at<=@end_date
                and id not in
                (
                   select id_patient from testing_mereenfant where date >=@start_date and date<@end_date
                 )
                )
                or id in
                (
                    select id_patient from testing_specimen where date_blood_taken >=@start_date and date_blood_taken <@end_date
                )
                or id in
                (
                    select id_patient from tracking_regime where created_at >=@start_date and created_at<=@end_date

                )
                or id in
                (
                    select id_patient from tracking_followup where date >=@start_date and date<=@end_date

                )

        )
    )

  )

) ) i , patient
where
#kids_club_program =1
#and

patient.id = i.id_patient
#and datediff(@end_date,dob) /365 <15
group by hospital_code
) table_for_left_join_with_lookup_hospital
ON table_for_left_join_with_lookup_hospital.hospital_code = lookup_hospital.hospital_code
order by hcode_nocity