SELECT
v.visitdate,
p.pid,
p.idcard,
CONCAT(ct.titlename,p.fname,' ',p.lname) as `name`,
CONCAT(DATE_FORMAT(p.birth,'%d/%m/'),
DATE_FORMAT(p.birth,'%Y')+543) AS `วันเดือนปีเกิด`,
TIMESTAMPDIFF(YEAR,p.BIRTH,NOW()) as `age(y)`,
if(sex='1','ชาย' ,if(sex='2','หญิง','')) as `sex`,
concat(h.hno,' ','หมู่',' ',p.mumoi,' ',vi.villname,' ',csubd.subdistname,' ',cdt.distname,' ',cp.provname) as `address`,
CONCAT(vdi.diagcode,'-',cd.diseasenamethai) as `diagcode`
from person p
INNER JOIN visit v on v.pcucodeperson=p.pcucodeperson AND v.pid = p.pid
INNER JOIN visitdiag vdi on vdi.visitno=v.visitno
INNER JOIN cdisease cd on cd.diseasecode=vdi.diagcode
INNER JOIN ctitle ct on ct.titlecode=p.PRENAME
LEFT JOIN house h on h.hcode=p.hcode
LEFT JOIN cprovince cp on cp.provcode=p.provcodemoi
LEFT JOIN cdistrict cdt on cdt.distcode=p.distcodemoi and cdt.provcode=p.provcodemoi
LEFT JOIN csubdistrict csubd on csubd.subdistcode=p.subdistcodemoi and csubd.distcode=p.distcodemoi and csubd.provcode=p.provcodemoi
LEFT JOIN village vi on vi.villcode=h.villcode
WHERE
LEFT(vdi.diagcode,3) BETWEEN 'F20' and 'F29' and
v.visitdate BETWEEN '2014-10-01' and '2015-09-30'
GROUP BY p.idcard
ORDER BY v.visitdate;