select
hospcode
,hosname
,count(distinct case when SCREENING = 1 AND COMPLICATION = 1 and 9_MONTH = 1 then cid end) works_9_month
,count(distinct case when SCREENING = 1 and 9_MONTH = 1 then cid end) target_9_month
,count(distinct case when SCREENING = 1 and COMPLICATION = 1 and 18_MONTH = 1 then cid end) works_18_month
,count(distinct case when SCREENING = 1 and 18_MONTH = 1 then cid end) target_18_month
,count(distinct case when SCREENING = 1 and COMPLICATION = 1 and 30_MONTH = 1 then cid end) works_30_month
,count(distinct case when SCREENING = 1 and 30_MONTH = 1 then cid end) target_30_month
,count(distinct case when SCREENING = 1 and COMPLICATION = 1 and 42_MONTH = 1 then cid end) works_42_month
,count(distinct case when SCREENING = 1 and 42_MONTH = 1 then cid end) target_42_month
from nhso_tmpqof00101_person
LEFT JOIN chospital_amp on nhso_tmpqof00101_person.HOSPCODE = chospital_amp.hoscode
where 9_MONTH = 1 or 18_MONTH = 1 or 30_MONTH = 1 or 42_MONTH = 1
group by hospcode