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 วันเกิด ,
YEAR( FROM_DAYS( DATEDIFF(CURDATE(),person.birth ) ) ) as อายุปี,
getagemonth(person.birth,CURDATE() )as อายุเดือน, 
CONCAT(DATE_FORMAT(epi10.dateepi,'%d/%m/'),DATE_FORMAT(epi10.dateepi,'%Y')+543)as 'BCG',
CONCAT(DATE_FORMAT(epi11.dateepi,'%d/%m/'),DATE_FORMAT(epi11.dateepi,'%Y')+543)as 'HBV1',
CONCAT(DATE_FORMAT(epi12.dateepi,'%d/%m/'),DATE_FORMAT(epi12.dateepi,'%Y')+543)as 'DTP1',
CONCAT(DATE_FORMAT(epi13.dateepi,'%d/%m/'),DATE_FORMAT(epi13.dateepi,'%Y')+543)as 'HBV2',
CONCAT(DATE_FORMAT(epi14.dateepi,'%d/%m/'),DATE_FORMAT(epi14.dateepi,'%Y')+543)as 'DTPHB1',
CONCAT(DATE_FORMAT(epi15.dateepi,'%d/%m/'),DATE_FORMAT(epi15.dateepi,'%Y')+543)as 'OPV1',
CONCAT(DATE_FORMAT(epi16.dateepi,'%d/%m/'),DATE_FORMAT(epi16.dateepi,'%Y')+543)as 'DTP2',
CONCAT(DATE_FORMAT(epi17.dateepi,'%d/%m/'),DATE_FORMAT(epi17.dateepi,'%Y')+543)as 'DTPHB2',
CONCAT(DATE_FORMAT(epi18.dateepi,'%d/%m/'),DATE_FORMAT(epi18.dateepi,'%Y')+543)as 'OPV2',
CONCAT(DATE_FORMAT(epi19.dateepi,'%d/%m/'),DATE_FORMAT(epi19.dateepi,'%Y')+543)as 'HBV3',
CONCAT(DATE_FORMAT(epi20.dateepi,'%d/%m/'),DATE_FORMAT(epi20.dateepi,'%Y')+543)as 'DTPHB3',
CONCAT(DATE_FORMAT(epi21.dateepi,'%d/%m/'),DATE_FORMAT(epi21.dateepi,'%Y')+543)as 'OPV3',
CONCAT(DATE_FORMAT(epi22.dateepi,'%d/%m/'),DATE_FORMAT(epi22.dateepi,'%Y')+543)as 'MEAS/MMR',
CONCAT(DATE_FORMAT(epi23.dateepi,'%d/%m/'),DATE_FORMAT(epi23.dateepi,'%Y')+543)as 'DTP4',
CONCAT(DATE_FORMAT(epi24.dateepi,'%d/%m/'),DATE_FORMAT(epi24.dateepi,'%Y')+543)as 'OPV4',
CONCAT(DATE_FORMAT(epi25.dateepi,'%d/%m/'),DATE_FORMAT(epi25.dateepi,'%Y')+543)as 'JE1',
CONCAT(DATE_FORMAT(epi26.dateepi,'%d/%m/'),DATE_FORMAT(epi26.dateepi,'%Y')+543)as 'JE2',
CONCAT(DATE_FORMAT(epi27.dateepi,'%d/%m/'),DATE_FORMAT(epi27.dateepi,'%Y')+543)as 'LJE1',
CONCAT(DATE_FORMAT(epi28.dateepi,'%d/%m/'),DATE_FORMAT(epi28.dateepi,'%Y')+543)as 'LJE2',
CONCAT(DATE_FORMAT(epi29.dateepi,'%d/%m/'),DATE_FORMAT(epi29.dateepi,'%Y')+543)as 'JE3',
CONCAT(DATE_FORMAT(epi30.dateepi,'%d/%m/'),DATE_FORMAT(epi30.dateepi,'%Y')+543)as 'DTP5',
CONCAT(DATE_FORMAT(epi31.dateepi,'%d/%m/'),DATE_FORMAT(epi31.dateepi,'%Y')+543)as 'OPV5',
CONCAT(DATE_FORMAT(epi32.dateepi,'%d/%m/'),DATE_FORMAT(epi32.dateepi,'%Y')+543)as 'MMR2',
CONCAT(DATE_FORMAT(epi32.dateepi,'%d/%m/'),DATE_FORMAT(epi32.dateepi,'%Y')+543)as 'MRC'

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 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice 
from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='010')as epi10 on person.pid=epi10.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice 
from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='041')as epi11 on person.pid=epi11.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='031')as epi12 on person.pid=epi12.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='042')as epi13 on person.pid=epi13.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='091')as epi14 on person.pid=epi14.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='081')as epi15 on person.pid=epi15.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='032')as epi16 on person.pid=epi16.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='092')as epi17 on person.pid=epi17.pid 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='082')as epi18 on person.pid=epi18.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='043')as epi19 on person.pid=epi19.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='093')as epi20 on person.pid=epi20.pid 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='083')as epi21 on person.pid=epi21.pid 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='061')as epi22 on person.pid=epi22.pid 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='034')as epi23 on person.pid=epi23.pid 
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='084')as epi24 on person.pid=epi24.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='051')as epi25 on person.pid=epi25.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='052')as epi26 on person.pid=epi26.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='J11')as epi27 on person.pid=epi27.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='J12')as epi28 on person.pid=epi28.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='053')as epi29 on person.pid=epi29.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi 
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode 
WHERE cdrug.files18epi='035')as epi30 on person.pid=epi30.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE cdrug.files18epi='085')as epi31 on person.pid=epi31.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE cdrug.files18epi='073')as epi32 on person.pid=epi32.pid
LEFT JOIN (select visitepi.pid,visitepi.dateepi,visitepi.vaccinecode,visitepi.pcucode,visitepi.pcucodeperson, visitepi.hosservice from visitepi
INNER JOIN cdrug on visitepi.vaccinecode=cdrug.drugcode
WHERE cdrug.files18epi='072')as epi33 on person.pid=epi33.pid
WHERE person.typelive in ('0','1','3')and person.birth between '20081001' and '20150930'
GROUP BY CONCAT(person.pcucodeperson,person.pid)
ORDER BY person.birth;