SELECT
person.pid,
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) AS 'วันเกิด',
getagemonth(person.birth,CURDATE()) AS 'อายุ(เดือน)',
GetAgeYearNum(person.birth,CURRENT_DATE)as 'อายุ(ปี)',
person.hnomoi AS 'บ้านเลขที่',
person.mumoi AS 'หมู่ที่',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-06-01' and '2012-01-31' and visitepi.vaccinecode = 'MRC' THEN visitepi.dateepi ELSE '' END) AS 'MRC'
FROM person 
INNER JOIN visitepi ON person.pcucodeperson = visitepi.pcucodeperson AND person.pid = visitepi.pid
inner join ctitle on person.prename = ctitle.titlecode
INNER JOIN house ON person.pcucodeperson = house.pcucode
AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode
AND village.villcode = house.villcode
WHERE (person.dischargetype is null or person.dischargetype = 9) 
and SUBSTRING(house.villcode,7,2) !='00'
and person.typelive in ('1','3') 
AND person.birth between '2008-06-01' and '2012-01-31'
group by person.pid
order by house.villcode;
#create by rambo