SELECT
person.pid
,concat(ctitle.titlename,person.fname," ",person.lname) as 'ชื่อ-สกุล'
,CONCAT(DATE_FORMAT(ncd_person_ncd_screen.screen_date,'%d/%m/')
,DATE_FORMAT(ncd_person_ncd_screen.screen_date,'%Y')+543) AS 'วันที่คัดกรองNCD'
,CONCAT(DATE_FORMAT(persondeath.deaddate,'%d/%m/'),DATE_FORMAT(persondeath.deaddate,'%Y')+543) AS 'วันที่เสียชีวิต'
,person.dischargetype AS 'สถานะจำหน่ายตาย'
FROM person
INNER JOIN ncd_person_ncd_screen ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
left JOIN ctitle on person.prename=ctitle.titlecode
WHERE ncd_person_ncd_screen.screen_date > persondeath.deaddate
AND ncd_person_ncd_screen.screen_date BETWEEN '2013-10-01' AND '2014-09-30';