SELECT concat(ctitle.titlename,person.fname,' ',person.lname,' ','(',convert(person.pid using utf8),')')as pname,
person.birth AS pbirth,
GetAgeYearNum(person.birth,'2013-10-01')as age,
person.idcard AS pcid,
person.hnomoi AS hno,
person.mumoi AS mu
FROM person 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.typelive IN ('1','3') and GetAgeYearNum(person.birth,CURRENT_DATE) >= '35'
and SUBSTRING(house.villcode,7,2)<>'00' and person.pid NOT IN (select ncd_person_ncd_screen.pid
FROM ncd_person_ncd_screen where ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30' )
and CONCAT(person.pid,person.pcucodeperson) not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE persondeath.deaddate <= CURRENT_DATE)and concat(person.pid,person.pcucodeperson)
NOT IN(select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'E1%' or(personchronic.chroniccode like'I1%'))
order BY village.villno
การตรวจสอบการบันทึกวัคซีนซ้ำ (ตัวหนังสือสีแดงเปลี่ยนชื่อวัคซีน)
SELECT pid, COUNT(pid) AS 'จำนวนซ้ำ'
FROM visitepi
WHERE visitepi.vaccinecode = 'DTP4'
GROUP BY pid
HAVING (COUNT(pid) > 1)