select q1.pcucode,chospital.hosname,count(distinct q1.pid)as 'จำนวนหญิงหลังคลอด'
,count(distinct case when q1.datecare1 is not null and q1.datecare2 is not null and q1.datecare3 is not null then q1.pid else null end)as 'หญิงหลังคลอดได้รับการเยี่ยมครบ 3 ครั้งตามเกณฑ์' from (
SELECT person.pcucodeperson as pcucode,person.pid,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS `name`,YEAR(FROM_DAYS(DATEDIFF(CURDATE(),person.birth))) AS Age,
person.idcard AS CID,concat(house.hno,' ','ม.',village.villno) AS Address,visitancdeliver.datedeliver
,vmc.datecare as datecare1,vmc2.datecare as datecare2,vmc3.datecare as datecare3
FROM person
INNER JOIN house ON person.hcode = house.hcode AND person.pcucodeperson = house.pcucode
INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN visitancdeliver ON person.pcucodeperson = visitancdeliver.pcucodeperson AND person.pid = visitancdeliver.pid
left join (select vmc.pid,vmc.pregno,vmc.datecare from visitancmothercare vmc)vmc on visitancdeliver.pid = vmc.pid and visitancdeliver.pregno = vmc.pregno
and getAgeDayNum(visitancdeliver.datedeliver,vmc.datecare)between 1 and 7
left join (select vmc.pid,vmc.pregno,vmc.datecare from visitancmothercare vmc)vmc2 on visitancdeliver.pid = vmc2.pid and visitancdeliver.pregno = vmc2.pregno
and getAgeDayNum(visitancdeliver.datedeliver,vmc2.datecare)between 8 and 15
left join (select vmc.pid,vmc.pregno,vmc.datecare from visitancmothercare vmc)vmc3 on visitancdeliver.pid = vmc3.pid and visitancdeliver.pregno = vmc3.pregno
and getAgeDayNum(visitancdeliver.datedeliver,vmc3.datecare)between 16 and 42
WHERE right(village.villcode,2)!= '00'and person.typelive in (0,1,3)and person.dischargetype=9
and visitancdeliver.datedeliver between '2014-10-01' and '2015-09-30'
GROUP BY person.pid order by village.villno,house.hno*1
)q1 inner join chospital on q1.pcucode=chospital.hoscode group by q1.pcucode;