SELECT
concat(ctitle.titlename,person.fname,' ',person.lname)as pname,
person.birth as birth,
person.hnomoi,person.mumoi,
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-03-3' and visitepi.vaccinecode = 'DTP4' THEN visitepi.dateepi ELSE '' END) AS 'DTP4',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-03-3' and visitepi.vaccinecode = 'OPV4' THEN visitepi.dateepi ELSE '' END) AS 'OPV4',
max(CASE WHEN person.typelive in ('1','3') AND person.birth between '2011-10-01' and '2012-03-3' and visitepi.vaccinecode in ('JE1','J11') THEN visitepi.dateepi ELSE '' END) AS 'JE1'
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 '2011-10-01' and '2012-03-3'
group by person.pid
order by house.villcode