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