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