SELECT CONCAT(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'ชื่อ-สกุล'
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS 'อายุ'
,person.hnomoi as 'บ้านเลขที่'
,SUBSTRING(house.villcode,7,2) AS 'หมู่มี่'
,visitdiag.diagcode as 'วินิจฉัยโรค'
,CONCAT(DATE_FORMAT(visit.visitdate,'%d-%m-'),DATE_FORMAT(visit.visitdate,'%Y')+543) AS 'วันที่รับการคัดกรอง'
,IF (visitscreenspecialdisease.codescreen = 0,'ปกติ','ผิดปกติ') AS 'ผลการคัดกรอง'
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN visit ON visit.pcucodeperson = person.pcucodeperson AND visit.pid = person.pid
INNER JOIN visitdiag ON visitdiag.pcucode = visit.pcucode AND visitdiag.visitno = visit.visitno
INNER JOIN visitscreenspecialdisease ON visitscreenspecialdisease.pcucode = visitdiag.pcucode AND visitscreenspecialdisease.visitno = visitdiag.visitno
WHERE persondeath.pid IS NULL
AND visitdiag.diagcode = 'Z11.1'
AND visitscreenspecialdisease.codescreen = 'c04'
AND visit.visitdate BETWEEN '2013-10-01' AND NOW()
ORDER BY villno,hnomoi*1;