select *

from (

select p.pid,concat(ctitle.titlename,p.fname,' ',p.lname) as name,p.idcard,concat(substr(p.birth,9,2),'/',substr(p.birth,6,2),'/',substr(p.birth,1,4)+543) as birth

,getAgeMonth(p.birth,CURDATE()) AS ageM,house.hno AS hno,village.villno AS moo

,max(case when ve.vaccinecode='BCG' then ve.dateepi else null end) as 'BCG'

,max(case when ve.vaccinecode='HBV1' then ve.dateepi else null end) as 'HBV1'

,max(case when ve.vaccinecode='HBV2' then ve.dateepi else null end) as 'HBV2'

,max(case when ve.vaccinecode='DTP1' then ve.dateepi else null end) as 'DTP1'

,max(case when ve.vaccinecode='DHB1' then ve.dateepi else null end) as 'DHB1'

,max(case when ve.vaccinecode='OPV1' then ve.dateepi else null end) as 'OPV1'

,max(case when ve.vaccinecode='DTP2' then ve.dateepi else null end) as 'DTP2'

,max(case when ve.vaccinecode='DHB2' then ve.dateepi else null end) as 'DHB2'

,max(case when ve.vaccinecode='OPV2' then ve.dateepi else null end) as 'OPV2'

,max(case when ve.vaccinecode='DTP3' then ve.dateepi else null end) as 'DTP3'

,max(case when ve.vaccinecode='DHB3' then ve.dateepi else null end) as 'DHB3'

,max(case when ve.vaccinecode='OPV3' then ve.dateepi else null end) as 'OPV3'

,max(case when ve.vaccinecode='MEAS' then ve.dateepi else null end) as 'MEAS'

,max(case when ve.vaccinecode='MMR' then ve.dateepi else null end) as 'MMR'

,max(case when ve.vaccinecode='DTP4' then ve.dateepi else null end) as 'DTP4'

,max(case when ve.vaccinecode='OPV4' then ve.dateepi else null end) as 'OPV4'

,max(case when ve.vaccinecode='JE1' then ve.dateepi else null end) as 'JE1'

,max(case when ve.vaccinecode='JE2' then ve.dateepi else null end) as 'JE2'

,max(case when ve.vaccinecode='JE3' then ve.dateepi else null end) as 'JE3'

,max(case when ve.vaccinecode='J11' then ve.dateepi else null end) as 'LAJE1'

,max(case when ve.vaccinecode='J12' then ve.dateepi else null end) as 'LAJE2'

,max(case when ve.vaccinecode='DTP5' then ve.dateepi else null end) as 'DTP5'

,max(case when ve.vaccinecode='OPV5' then ve.dateepi else null end) as 'OPV5'

FROM person p

LEFT JOIN ctitle ON ctitle.titlecode = p.prename

INNER JOIN house ON p.pcucodeperson = house.pcucode AND p.hcode = house.hcode

INNER JOIN village ON house.villcode = village.villcode AND house.pcucode = village.pcucode

left join (select ve.pid,ve.dateepi,ve.vaccinecode from visitepi ve) ve on p.pid=ve.pid

where getAgemonth(p.birth,CURDATE()) <=60 and right(village.villcode,2)!= '00' and typelive in (0,1,3) and p.dischargetype=9

group by p.pid order by moo,hno*1

)q1 where BCG is null or (HBV1 is null and HBV2 is null) 

or ((DTP1 is null and DHB1 is null) and q1.ageM >2) or (OPV1 is null and q1.ageM >2)

or ((DTP2 is null and DHB2 is null) and q1.ageM >4) or (OPV2 is null and q1.ageM >4)

or ((DTP3 is null and DHB3 is null) and q1.ageM >6) or (OPV3 is null and q1.ageM >6)

or ((MEAS is null and MMR is null) and q1.ageM >9)

or (DTP4 is null and q1.ageM > 18)or (OPV4 is null and q1.ageM >18 )

or ((JE1 is null and q1.ageM >18)and LAJE1 is null)

or ((JE2 is null and q1.ageM >20)and LAJE2 is null)

or ((JE3 is null and q1.ageM >30)and (LAJE1 is null or LAJE2 is null))

or (DTP5 is null and q1.ageM >48)or (OPV5 is null and q1.ageM >48);