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

select CONCAT(city_code, '/', hospital_code) AS hospital_code , count(*) as "know_status", count(*) as "surveyed",
#gender, dob ,
#datediff("2014-01-01",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(@start_date,dob) /365 >=0
			AND  datediff(@end_date,dob) /365 <1
		THEN
            1
        ELSE 0
    END) AS under_1,
    SUM(CASE
        WHEN
            datediff(@start_date,dob) /365 >=1
			AND  datediff(@end_date,dob) /365 <=4
        THEN
            1
        ELSE 0
    END) AS btwn_1_and_4,
    SUM(CASE
        WHEN
            datediff(@start_date,dob) /365 >4
			AND  datediff(@end_date,dob) /365 <=9
        THEN
            1
        ELSE 0
    END) AS btwn_5_and_9,
    SUM(CASE
        WHEN
            datediff(@start_date,dob) /365 >9
			AND  datediff(@end_date,dob) /365 <15
        THEN
            1
        ELSE 0
    END) AS btwn_9_and_15    
    
from tracking_infant i , patient
where
 id_patient in (select id_patient from club_patient)
and patient.id = i.id_patient
and datediff(@start_date,dob) /365 <15
and 
(id_patient in (select id_patient from testing_specimen where pcr_result = 1)
or id_patient in (select id_patient from tracking_regime)
or id_patient in (select id_patient from tracking_followup)
or id_patient in (select id_patient from testing_result where result = 1)
)
group by hospital_code