SELECT h.off_id as hospcode,h.off_name as hospname ,count(DISTINCT(if(s.PID is NOT NULL,s.PID,NULL))) as result , count(DISTINCT(if(lb.BDATE is NOT NULL,lb.PID,NULL))) as target, ROUND(count(DISTINCT(if(s.PID is NOT NULL,s.PID,NULL)))/count(DISTINCT(if(lb.BDATE is NOT NULL,lb.PID,NULL))) *100,2) as percent FROM co_office h left join (select l.HOSPCODE,l.PID,l.BDATE,l.GRAVIDA,p.TYPEAREA from labor l inner join person p on l.HOSPCODE=p.HOSPCODE and l.PID=p.PID and l.BDATE<>" " where p.TYPEAREA in(1,3) and p.discharge = 9 -- and p.nation=099 and l.BDATE BETWEEN "20181001" and "20190930" ) lb on h.off_id=lb.HOSPCODE left join ( SELECT a1.HOSPCODE,a1.PID,a1.GRAVIDA,a1.bdate as g1,a2.bdate as g2,a3.bdate as g3 from postnatal a1 INNER JOIN postnatal a2 on a1.HOSPCODE=a2.HOSPCODE AND a1.PID=a2.PID and datediff(a2.PPCARE,a2.bdate) between 8 and 15 INNER JOIN postnatal a3 on a1.HOSPCODE=a3.HOSPCODE AND a1.PID=a3.PID and datediff(a3.PPCARE,a3.bdate) between 16 and 42 WHERE datediff(a1.PPCARE,a1.bdate) <=7 ) s on lb.HOSPCODE=s.HOSPCODE and s.PID=lb.PID and s.GRAVIDA=lb.GRAVIDA where h.hasdata="Y" and left(h.subdistid,4) ="6701" group by h.off_id UNION SELECT "รวมทั้งคัพ" as hospcode, "" as hospname ,count(DISTINCT(if(s.PID is NOT NULL,s.PID,NULL))) as result , count(DISTINCT(if(lb.BDATE is NOT NULL,lb.PID,NULL))) as target, ROUND(count(DISTINCT(if(s.PID is NOT NULL,s.PID,NULL)))/count(DISTINCT(if(lb.BDATE is NOT NULL,lb.PID,NULL))) *100,2) as percent FROM co_office h left join (select l.HOSPCODE,l.PID,l.BDATE,l.GRAVIDA,p.TYPEAREA from labor l inner join person p on l.HOSPCODE=p.HOSPCODE and l.PID=p.PID and l.BDATE<>" " where p.TYPEAREA in(1,3) and p.discharge = "9" -- and p.nation=099 and l.BDATE BETWEEN "20181001" and "20190930" ) lb on h.off_id=lb.HOSPCODE left join ( SELECT a1.HOSPCODE,a1.PID,a1.GRAVIDA,a1.bdate as g1,a2.bdate as g2,a3.bdate as g3 from postnatal a1 INNER JOIN postnatal a2 on a1.HOSPCODE=a2.HOSPCODE AND a1.PID=a2.PID and datediff(a2.PPCARE,a2.bdate) between 8 and 15 INNER JOIN postnatal a3 on a1.HOSPCODE=a3.HOSPCODE AND a1.PID=a3.PID and datediff(a3.PPCARE,a3.bdate) between 16 and 42 WHERE datediff(a1.PPCARE,a1.bdate) <=7 ) s on lb.HOSPCODE=s.HOSPCODE and s.PID=lb.PID and s.GRAVIDA=lb.GRAVIDA where h.hasdata="Y" and left(h.subdistid,4) ="6701"