SELECT
concat(ctitle.titlename,person.fname," ",person.lname)as'ชื่อ-สกุล',
house.hno as ที่อยู่,
village.villno as หมู่,
village.villname as บ้าน,
person.typelive,
CONCAT(DATE_FORMAT(person.birth,'%d/%m/'),DATE_FORMAT(person.birth,'%Y')+543)as วันเกิด ,
getagemonth(person.birth,CURDATE() )as อายุเดือน,
case when person.birth between 20140101 and 20140131 THEN '9'
when person.birth BETWEEN 20130401 and 20130430 then '18'
when person.birth BETWEEN 20120401 and 20120430 then '30'
when person.birth BETWEEN 20110401 and 20110430 THEN '42' else null end as ตุลาคม57,
case when person.birth BETWEEN 20140201 and 20140228 THEN '9'
when person.birth BETWEEN 20130501 and 20130531 THEN '18'
when person.birth BETWEEN 20120501 and 20120531 then '30'
when person.birth BETWEEN 20110501 and 20110531 THEN '42' else null end as พฤศจิกายน57,
case when person.birth BETWEEN 20140301 and 20140331 THEN '9'
when person.birth BETWEEN 20130601 and 20130630 THEN '18'
when person.birth BETWEEN 20120601 and 20120630 then '30'
when person.birth BETWEEN 20110601 and 20110630 THEN '42' else null end as ธันวาคม57,
case when person.birth BETWEEN 20140401 and 20140430 THEN '9'
when person.birth BETWEEN 20130701 and 20130731 THEN '18'
when person.birth BETWEEN 20120701 and 20120731 then '30'
when person.birth BETWEEN 20110701 and 20110731 THEN '42' else null end as มกราคม58,
case when person.birth BETWEEN 20140501 and 20140531 THEN '9'
when person.birth BETWEEN 20130801 and 20130831 THEN '18'
when person.birth BETWEEN 20120801 and 20120831 then '30'
when person.birth BETWEEN 20110801 and 20110831 THEN '42' else null end as กุมภาพันธ์58,
case when person.birth BETWEEN 20140601 and 20140630 THEN '9'
when person.birth BETWEEN 20130901 and 20130930 THEN '18'
when person.birth BETWEEN 20120901 and 20120930 then '30'
when person.birth BETWEEN 20110901 and 20110930 THEN '42' else null end as มีนาคม58,
case when person.birth BETWEEN 20140701 and 20140731 THEN '9'
when person.birth BETWEEN 20131001 and 20131031 THEN '18'
when person.birth BETWEEN 20121001 and 20121031 then '30'
when person.birth BETWEEN 20111001 and 20111031 THEN '42' else null end as เมษายน58,
case when person.birth BETWEEN 20140801 and 20140831 THEN '9'
when person.birth BETWEEN 20131101 and 20131130 THEN '18'
when person.birth BETWEEN 20121101 and 20121130 then '30'
when person.birth BETWEEN 20111101 and 20111130 THEN '42' else null end as พฤษภาคม58,
case when person.birth BETWEEN 20140901 and 20140930 THEN '9'
when person.birth BETWEEN 20131201 and 20131231 THEN '18'
when person.birth BETWEEN 20121201 and 20121231 then '30'
when person.birth BETWEEN 20111201 and 20111230 THEN '42' else null end as มิถุนายน58,
case when person.birth BETWEEN 20141001 and 20141031 THEN '9'
when person.birth BETWEEN 20140101 and 20140131 THEN '18'
when person.birth BETWEEN 20130101 and 20130131 then '30'
when person.birth BETWEEN 20120101 and 20120131 THEN '42' else null end as กรกฎาคม58,
case when person.birth BETWEEN 20141101 and 20141130 THEN '9'
when person.birth BETWEEN 20140201 and 20140228 THEN '18'
when person.birth BETWEEN 20130201 and 20130228 then '30'
when person.birth BETWEEN 20120201 and 20120228 THEN '42' else null end as สิงหาาคม58,
case when person.birth BETWEEN 20141201 and 20141231 THEN '9'
when person.birth BETWEEN 20140301 and 20140331 THEN '18'
when person.birth BETWEEN 20130301 and 20130331 then '30'
when person.birth BETWEEN 20120301 and 20120331 THEN '42' else null end as กันยายน58

FROM person
INNER JOIN house on person.hcode=house.hcode AND person.pcucodeperson=house.pcucodeperson
INNER JOIN village on house.villcode = village.villcode and house.pcucode = village.pcucode
INNER JOIN ctitle on person.prename=ctitle.titlecode
WHERE person.typelive in ('0','1','3') and right(house.villcode,2)!='00' and person.birth between '20091001' and '20150930' 
and getagemonth(person.birth,CURDATE() ) <= 72 AND
CONCAT(person.pid,person.pcucodeperson) NOT IN (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE deaddate <= CURDATE() or deaddate is not NULL) AND ((person.dischargetype is null) OR (person.dischargetype != '1')) # คัดคนตายออกไป
GROUP BY person.birth