SELECT p.pid,
concat(p.fname,' ',p.lname) as personname,
p.idcard,timestampdiff(year,p.birth,vp.lmp) AS age,
p.hnomoi,p.villno AS หมู่ ,vp.lmp,vp.edc,t.datedeliver AS วันคลอด,t.pregno,
t1.pregage AS ANC1_p_age,t1.datecheck AS ANC1_d_check,
t2.pregage AS ANC2_p_gag,t2.datecheck AS ANC2_d_check,
t3.pregage AS ANC3_p_gag,t3.datecheck AS ANC3_d_check,
t4.pregage AS ANC4_p_gag,t4.datecheck AS ANC4_d_check,
t5.pregage AS ANC5_p_gag,t5.datecheck AS ANC5_d_check
FROM
(SELECT * FROM visitancdeliver v WHERE v.datedeliver BETWEEN '2014-10-01' AND '2015-09-30' ORDER BY v.datedeliver ) t
LEFT JOIN visitancpregnancy vp ON vp.pcucodeperson=t.pcucodeperson AND vp.pid=t.pid AND vp.pregno=t.pregno
INNER JOIN (SELECT ps.*,village.villno FROM person ps
INNER JOIN house ON ps.pcucodeperson = house.pcucode AND ps.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode where right(house.villcode,2)!='00') p ON p.pid=t.pid AND p.pcucodeperson=t.pcucodeperson 
LEFT JOIN (SELECT * FROM visitanc WHERE pregage<=12 GROUP BY pid,pregno) t1 ON t1.pid=t.pid AND t1.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 16 AND 20 GROUP BY pid,pregno) t2 ON t2.pid=t.pid AND t2.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 24 AND 28 GROUP BY pid,pregno) t3 ON t3.pid=t.pid AND t3.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 30 AND 34 GROUP BY pid,pregno) t4 ON t4.pid=t.pid AND t4.pregno=t.pregno
LEFT JOIN (SELECT * FROM visitanc WHERE pregage BETWEEN 36 AND 40 GROUP BY pid,pregno) t5 ON t5.pid=t.pid AND t5.pregno=t.pregno
