SELECT
person.pid AS 'Hn',
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
person.birth as 'ว/ด/ปเกิด',
person.idcard AS 'เลขบัตรปชช',
getageymd (person.birth,CURRENT_DATE()) as 'อายุ',
house.hno as 'บ้านเลขที่',
village.villno as 'หมู่ที่',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22'and visitepi.vaccinecode = 'BCG' THEN visitepi.dateepi ELSE '' END) AS 'BCG',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22'and visitepi.vaccinecode = 'DHB1' THEN visitepi.dateepi ELSE '' END) AS 'DHB1',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'OPV1' THEN visitepi.dateepi ELSE '' END) AS 'OPV1',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'DHB2' THEN visitepi.dateepi ELSE '' END) AS 'DHB2',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'OPV2' THEN visitepi.dateepi ELSE '' END) AS 'OPV2',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'DHB3' THEN visitepi.dateepi ELSE '' END) AS 'DHB3',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'OPV3' THEN visitepi.dateepi ELSE '' END) AS 'OPV3',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'MMR' THEN visitepi.dateepi ELSE '' END) AS 'MMR',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'DTP4' THEN visitepi.dateepi ELSE '' END) AS 'DTP4',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'OPV4' THEN visitepi.dateepi ELSE '' END) AS 'OPV4',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'JE1' THEN visitepi.dateepi ELSE '' END) AS 'JE1',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'JE2' THEN visitepi.dateepi ELSE '' END) AS 'JE2',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'JE3' THEN visitepi.dateepi ELSE '' END) AS 'JE3',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'DTP5' THEN visitepi.dateepi ELSE '' END) AS 'DTP5',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2008-10-01'and '2014-03-22' and visitepi.vaccinecode = 'OPV5' THEN visitepi.dateepi ELSE '' END) AS 'OPV5'
FROM person left 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 = 9
and SUBSTRING(house.villcode,7,2) !='00'
and person.typelive in ('1','3')
AND person.birth BETWEEN '2009-07-01' and '2014-03-31'
group by person.pid
ORDER BY village.villno;