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_tmpqof0021_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 "20180401" and "20180331" then 1 end) sum_work from nhso_tmpqof0021_person1 where (CHRONIC is null or (CHRONIC is not null and DATE_DIAG < DATE_SERV_NCDSCREEN)) and SBP_1 >= 30 and DBP_1 >= 20 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