OVC_SERV with all disagg age and gender

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

SELECT lookup_hospital.hospital_code, total,
m_under_1, f_under_1, m_btwn_1_and_4, f_btwn_1_and_4, m_btwn_5_and_9, f_btwn_5_and_9, m_btwn_10_and_14, f_btwn_10_and_14 , m_btwn_15_and_17, f_btwn_15_and_17 , m_btwn_18_and_24, f_btwn_18_and_24, m_25plus, f_25plus
 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 ,

    SUM(CASE
        WHEN
            dob BETWEEN (@start_date - INTERVAL 364 DAY) AND @end_date
            AND dob != '0000-00-00'
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_under_1,
        SUM(CASE
        WHEN
            dob BETWEEN (@start_date - INTERVAL 364 DAY) AND @end_date
            AND dob != '0000-00-00'
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_under_1,
    SUM(CASE
        WHEN
            dob BETWEEN (@start_date - INTERVAL 4 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

        THEN
            1
        ELSE 0
    END) AS m_btwn_1_and_4,
        SUM(CASE
        WHEN
            dob BETWEEN (@start_date - INTERVAL 4 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
        THEN
            1
        ELSE 0
    END) AS f_btwn_1_and_4,
    SUM(CASE
        WHEN
              dob BETWEEN (@start_date - INTERVAL 9 YEAR + INTERVAL 1 DAY) AND @end_date - INTERVAL 5 YEAR
              AND dob != '0000-00-00' AND timestampdiff(YEAR, dob, @start_date)>= 5
              AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_btwn_5_and_9,
        SUM(CASE
        WHEN
              dob BETWEEN (@start_date - INTERVAL 9 YEAR + INTERVAL 1 DAY) AND @end_date - INTERVAL 5 YEAR
              AND dob != '0000-00-00' AND timestampdiff(YEAR, dob, @start_date)>= 5
              AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_btwn_5_and_9,
    SUM(CASE
        WHEN
            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
        THEN
            1
        ELSE 0
    END) AS m_btwn_10_and_14,
    SUM(CASE
        WHEN
            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
        THEN
            1
        ELSE 0
    END) AS f_btwn_10_and_14,
    SUM(CASE
        WHEN
             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
        THEN
            1
        ELSE 0
    END) AS m_btwn_15_and_17,
    SUM(CASE
        WHEN
             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
        THEN
            1
        ELSE 0
    END) AS f_btwn_15_and_17,
    SUM(CASE
        WHEN
            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
        THEN
            1
        ELSE 0
    END) AS m_btwn_18_and_24,
    SUM(CASE
        WHEN
            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
        THEN
            1
        ELSE 0
    END) AS f_btwn_18_and_24,
    SUM(CASE
        WHEN
            dob <= @end_date - INTERVAL 25 YEAR AND dob != '0000-00-00' AND timestampdiff(YEAR, dob, @start_date)>= 25
            AND gender = 1
        THEN
            1
        ELSE 0
    END) AS m_25plus,
    SUM(CASE
        WHEN
            dob <= @end_date - INTERVAL 25 YEAR AND dob != '0000-00-00' AND timestampdiff(YEAR, dob, @start_date)>= 25
            AND gender = 2
        THEN
            1
        ELSE 0
    END) AS f_25plus
FROM
/*
(
select id_patient, infant_dob as dob , infant_gender as gender
from testing_mereenfant
where date >="2013-10-01" and date<@start_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

                )
                OR id IN
                (
                    SELECT id_patient FROM SESSION LEFT JOIN club_session ON SESSION.id_club_session = club_session.id
                    LEFT JOIN club ON club.id = club_session.id_club
                    WHERE club_session.DATE >=@start_date AND club_session.date<=@end_date AND club.club_type != 1
                )
                OR id IN
                (
                    SELECT id_patient FROM tracking_infant WHERE  positive_pcr_1 >=@start_date AND positive_pcr_1 <@end_date OR
                    positive_pcr_2 >=@start_date AND positive_pcr_2 <@end_date
                )

        )
    )

  )

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

patient.id = i.id_patient
#and datediff(@start_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