select
targets.hospcode
,targets.right_new
,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
,right_new
,count(cid) target
from nhso_tmpqof0011_person1
where (CHRONIC is null or (CHRONIC is not null and DATE_DIAG < DATE_SERV_NCDSCREEN)) and RIGHT_NEW in ("UCS","OTHER")
group by hospcode, right_new
) targets
left join
(
select hospcode
,right_new
,count(case when DATE_SERV_NCDSCREEN between "20170401" and "20170630" then 1 end) work_t3
,count(case when DATE_SERV_NCDSCREEN between "20170701" and "20170930" then 1 end) work_t4
,count(case when DATE_SERV_NCDSCREEN between "20171001" and "20171231" then 1 end) work_t1
,count(case when DATE_SERV_NCDSCREEN between "20180101" and "20180331" then 1 end) work_t2
,count(case when DATE_SERV_NCDSCREEN between "20170401" and "20180331" then 1 end) sum_work
from nhso_tmpqof0011_person1
where (CHRONIC is null or (CHRONIC is not null and DATE_DIAG < DATE_SERV_NCDSCREEN))
and bstest in ("1","2","3","4") and bslevel > 0
and (DATE_SERV_DIAG is null or DATE_SERV_DIAG >= DATE_SERV_NCDSCREEN) and RIGHT_NEW in ("UCS","OTHER")
group by hospcode, right_new
) works on works.hospcode = targets.hospcode and works.right_new = targets.right_new
order by works.hospcode, works.right_new