select targets.hospcode ,chospital_amp.hosname ,ifnull(works.work_t3, 0) work_t3 ,ifnull(works.work_t4, 0) work_t4 ,ifnull(works.work_t1, 0) work_t1 ,ifnull(works.work_t2, 0) work_t2 ,ifnull(works.sum_work, 0) sum_work ,targets.target from ( select hospcode ,count(distinct concat(pid, hospcode)) target from nhso_tmpqof003_anc group by hospcode ) targets left join ( select hospcode ,count(distinct case when DATE_SERV between "20170401" and "20170630" then concat(pid, hospcode) end) work_t3 ,count(distinct case when DATE_SERV between "20170701" and "20170930" then concat(pid, hospcode) end) work_t4 ,count(distinct case when DATE_SERV between "20171001" and "20171231" then concat(pid, hospcode) end) work_t1 ,count(distinct case when DATE_SERV between "20180101" and "20180331" then concat(pid, hospcode) end) work_t2 ,count(distinct case when DATE_SERV between "20170401" and "20180331" then concat(pid, hospcode) end) sum_work from nhso_tmpqof003_anc where ANCNO = 1 and GA <= 12 group by hospcode ) works on works.hospcode = targets.hospcode LEFT JOIN chospital_amp ON chospital_amp.hoscode = targets.hospcode