set @str = '2014-10-01';
set @sto = '2015-09-30';

SELECT
ancq.*,
if(vdl.datedeliver is null,'ยังไม่คลอด','คลอดแล้ว') as statusd,
vdl.datedeliver,
## เยี่ยมหลังคลอด
(SELECT min(vamcs.datecare) FROM visitancmothercare vamcs where datediff(vamcs.datecare,vdl.datedeliver) BETWEEN 1 and 7 and ancq.pcucodeperson = vamcs.pcucodeperson AND ancq.pid = vamcs.pid AND ancq.pregno = vamcs.pregno GROUP BY vamcs.pcucodeperson,vamcs.pid,vamcs.pregno ) as mch1,
(SELECT min(vamcs.datecare) FROM visitancmothercare vamcs where datediff(vamcs.datecare,vdl.datedeliver) BETWEEN 8 and 15 and ancq.pcucodeperson = vamcs.pcucodeperson AND ancq.pid = vamcs.pid AND ancq.pregno = vamcs.pregno GROUP BY vamcs.pcucodeperson,vamcs.pid,vamcs.pregno ) as mch2,
(SELECT min(vamcs.datecare) FROM visitancmothercare vamcs where datediff(vamcs.datecare,vdl.datedeliver) BETWEEN 16 and 42 and ancq.pcucodeperson = vamcs.pcucodeperson AND ancq.pid = vamcs.pid AND ancq.pregno = vamcs.pregno GROUP BY vamcs.pcucodeperson,vamcs.pid,vamcs.pregno ) as mch3
FROM
(SELECT
ancperson.*,
#‪#‎ฝากครรภ์‬ 5 ครั้งคุณภาพ
(select DATE_FORMAT(MAX(v1.datecheck),'%Y-%m-%d') from visitanc v1 inner JOIN visitancpregnancy p1 on p1.pcucodeperson = v1.pcucodeperson and v1.pid = p1.pid and p1.pregno = v1.pregno where v1.pid = ancperson.pid and v1.pcucodeperson = ancperson.pcucodeperson and DATEDIFF(v1.datecheck,p1.lmp) <= 90 and v1.pregno = ancperson.pregno group by ancperson.pid,ancperson.pcucodeperson,ancperson.pregno) as anc1,
(select DATE_FORMAT(MAX(v2.datecheck),'%Y-%m-%d') from visitanc v2 inner JOIN visitancpregnancy p2 on p2.pcucodeperson = v2.pcucodeperson and v2.pid = p2.pid and p2.pregno = v2.pregno where v2.pid = ancperson.pid and v2.pcucodeperson = ancperson.pcucodeperson and DATEDIFF(v2.datecheck,p2.lmp) between 112 AND 146 and v2.pregno = ancperson.pregno group by ancperson.pid,ancperson.pcucodeperson,ancperson.pregno) as anc2,
(select DATE_FORMAT(MAX(v3.datecheck),'%Y-%m-%d') from visitanc v3 inner JOIN visitancpregnancy p3 on p3.pcucodeperson = v3.pcucodeperson and v3.pid = p3.pid and p3.pregno = v3.pregno where v3.pid = ancperson.pid and v3.pcucodeperson = ancperson.pcucodeperson and DATEDIFF(v3.datecheck,p3.lmp) between 168 AND 202 and v3.pregno = ancperson.pregno group by ancperson.pid,ancperson.pcucodeperson,ancperson.pregno) as anc3,
(select DATE_FORMAT(MAX(v4.datecheck),'%Y-%m-%d') from visitanc v4 inner JOIN visitancpregnancy p4 on p4.pcucodeperson = v4.pcucodeperson and v4.pid = p4.pid and p4.pregno = v4.pregno where v4.pid = ancperson.pid and v4.pcucodeperson = ancperson.pcucodeperson and DATEDIFF(v4.datecheck,p4.lmp) between 210 AND 244 and v4.pregno = ancperson.pregno group by ancperson.pid,ancperson.pcucodeperson,ancperson.pregno) as anc4,
(select DATE_FORMAT(MAX(v5.datecheck),'%Y-%m-%d') from visitanc v5 inner JOIN visitancpregnancy p5 on p5.pcucodeperson = v5.pcucodeperson and v5.pid = p5.pid and p5.pregno = v5.pregno where v5.pid = ancperson.pid and v5.pcucodeperson = ancperson.pcucodeperson and DATEDIFF(v5.datecheck,p5.lmp) between 252 AND 286 and v5.pregno = ancperson.pregno group by ancperson.pid,ancperson.pcucodeperson,ancperson.pregno) as anc5
FROM
(SELECT
## ข้อมูลทั่วไป
a.pcucodeperson,
a.pid,
p.fname,
concat(c.titlename, p.fname , ' ' , p.lname) AS pname,
p.birth,
FLOOR(datediff(now(),p.birth)/365.25) as age,
h.hno,
right(h.villcode,2) as moo,
a.pregno,
ap.lmp,
ap.edc,
floor(DATEDIFF(now(),ap.lmp)/7) as page
FROM
house as h
INNER JOIN person as p on p.pcucodeperson = h.pcucodeperson and p.hcode = h.hcode
LEFT JOIN ctitle as c on c.titlecode = p.prename
INNER JOIN visitancpregnancy as ap on ap.pcucodeperson = p.pcucodeperson and ap.pid = p.pid
INNER JOIN visitanc as a on ap.pcucodeperson = a.pcucodeperson and ap.pid = a.pid and ap.pregno = a.pregno
where ap.lmp BETWEEN @str and @sto and p.typelive in ('0','1','3') AND ((p.dischargetype is null) or (p.dischargetype = '9'))
GROUP BY a.pcucodeperson, a.pid, a.pregno) as ancperson
ORDER BY ancperson.pcucodeperson,ancperson.moo,ancperson.page) as ancq
left join visitancdeliver vdl on vdl.pcucodeperson = ancq.pcucodeperson and vdl.pid = ancq.pid and vdl.pregno = ancq.pregno