USE caris_db;
SET @start_date= '2018-07-01';
SET @end_date='2018-09-30';
select concat(lookup_hospital.city_code,'/',lookup_hospital.hospital_code) as hospital_code,  sum(f_under_1) as f_under_1, sum(f_1_4) as f_1_4, sum(f_5_9) as f_5_9, sum(f_10_14) as f_10_14, sum(f_15_17) as f_15_17, sum(f_18_25) as f_18_25, sum(f_25) as f_25, 
sum(m_under_1) as m_under_1, sum(m_1_4) as m_1_4, sum(m_5_9) as m_5_9, sum(m_10_14) as m_10_14, sum(m_15_17) as m_15_17, sum(m_18_25) as m_18_25, sum(m_25) as m_25
from tracking_familymember
left join (select distinct club_patient.id_patient, id_hospital from club_patient
left join club on club_patient.id_club = club.id
left join club_session on club_session.id_club=club_patient.id_club
left join session on session.id_patient = club_patient.id_patient
where is_present = 1 and date between @start_date and @end_date
)a
 on a.id_patient = tracking_familymember.id_patient

left join lookup_hospital on lookup_hospital.id = a.id_hospital
 
group by a.id_hospital