SELECT
person.pid,
DATE_SUB(Birth, INTERVAL -543 YEAR) AS 'วดปเกิด',
CONCAT(ctitle.titlename) AS `คำนำหน้า`,
fname AS `ชื่อ`,
lname AS `สกุล`,
timestampdiff(year,person.birth,date(NOW())) AS อายุปี,
timestampdiff(month,person.birth,date(NOW()))-(timestampdiff(year,person.birth,date(NOW()))*12) AS `เดือน`,
hnomoi AS 'บ้านเลขที่' ,
mumoi AS 'หมู่',
visitepi.vaccinecode AS วัคซีนที่เคยรับใน5ปี,
DATE_SUB(visitepi.dateepi, INTERVAL -543 YEAR) AS วันที่รับวัคซีน
FROM
person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN visitepi ON visitepi.pid = person.pid
WHERE
getAgeYearNum(person.birth,CURDATE()) BETWEEN '20' AND '50'
AND visitepi.dateepi BETWEEN '2011-10-01' and '2015-09-30'
AND visitepi.vaccinecode LIKE '%dt%'
AND person.birth BETWEEN '1964-01-01' AND '1994-01-01'
AND person.typelive in ('1','3')
AND person.dischargetype = '9'
AND person.pid not IN
(SELECT persondeath.pid
FROM persondeath)
AND person.pid not IN
(SELECT visitepi.pid FROM visitepi
WHERE visitepi.dateepi BETWEEN '2014-10-01' AND '2014-10-30')
ORDER BY mumoi asc;