SELECT
person.pid AS 'HN',
concat(ctitle.titlename,person.fname,' ',person.lname)as 'ชื่อ-สกุล',
date_format(person.birth,"%Y-%m-%d") AS pbirth,
GetAgeYearNum(person.birth,CURRENT_DATE)as age,
person.idcard AS 'เลขประจำตัวประชาชน',
person.sex AS sex,
person.hnomoi AS hno,
person.mumoi AS mu,
visit.visitdate,
visitdiag.diagcode,
visit.username
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 visit ON person.pid=visit.pid
AND visit.pcucode=person.pcucodeperson
INNER JOIN visitdiag ON visit.visitno=visitdiag.visitno
WHERE
person.typelive IN ('1','3')
#AND person.sex='2'
and person.pid not in (SELECT persondeath.pid FROM persondeath)
AND visitdate BETWEEN '2014-10-01' and '2014-10-31';
