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"