select
     person.pid as pid,
     concat(ctitle.titlename,person.fname,'    ',person.lname)as pname,
     age_year,
     person.idcard as pcid,
     person.typelive as 'สถานะการอยู่อาศัย',
     case when person.dischargetype = '1' then 'ตาย' when person.dischargetype = '2'then          'ย้ายออกนอกเขต' when person.dischargetype = '3'then 'สุญหาย'
     when person.dischargetype = '9'then 'ยังไม่จำหน่าย'else null end as 'สาเหตุจำหน่าย',
     person.hnomoi as hno,
     person.mumoi as mu,
     DATE_FORMAT(screen_date,'%Y-%m-%d') as screen_date
		 #persondeath.deaddate
from
     ncd_person_ncd_screen inner join person on ncd_person_ncd_screen.pid = person.pid
     inner join ctitle on person.prename = ctitle.titlecode
     inner join ncd_person  on ncd_person_ncd_screen.pcucode = ncd_person.pcucode and ncd_person_ncd_screen.pid = ncd_person.pid
		 #INNER JOIN persondeath ON person.pid=persondeath.pid
where
     age_year >= '15' and ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30'  and (person.typelive not in ('1','3') or person.dischargetype = '1')
group by person.pid
order by ncd_person.village;