SELECT person.pid AS HN
,CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ'
,person.idcard AS 'เลขบัตรประชาชน'
,CONCAT(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) AS 'วันเกิด'
,getagemonth(person.birth,CURDATE()) AS 'อายุ(เดือน)'
,house.hno AS 'บ้านเลขที่'
,village.villno AS 'หมู่'
,village.villname AS 'บ้าน'
,visitdrug.drugcode
,cdrug.drugname AS 'หัตถการ'
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
LEFT JOIN persondeath ON person.pid = persondeath.pid AND person.pcucodeperson = persondeath.pcucodeperson
INNER JOIN visit ON person.pid=visit.pid AND person.pcucodeperson=visit.pcucodeperson
INNER JOIN visitdrug ON visit.visitno=visitdrug.visitno AND visit.pcucode=visitdrug.pcucode
LEFT JOIN cdrug ON visitdrug.drugcode=cdrug.drugcode
WHERE persondeath.pid IS NULL
AND person.typelive IN ('0','1','3')
AND person.dischargetype='9'
AND right(house.villcode,2)!= '00'
AND getagemonth(person.birth,CURDATE()) <= 36
AND visit.visitdate BETWEEN '2014-09-01' AND '2015-09-30'
AND visitdrug.drugcode IN ('2377020','2377021')
GROUP BY person.pid
ORDER BY village.villno;