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