SELECT
person.pid AS HN
,concat(ctitle.titlename,' ',person.fname,' ',person.lname) AS 'ชื่อ-สกุล'
,YEAR( FROM_DAYS( DATEDIFF( '2012-10-01' ,person.birth ) ) ) as อายุ
,person.idcard as CID
,house.hno as 'บ้านลขที่',right((house.villcode),2) AS หมู่ที่
,concat(personchronic.chroniccode) as 'โรคเรื้อรัง'
,ncdservice.date_serv as 'วันที่ตรวจ'
,case when ncdservice.exameyel=1 then 'NO DR'
when ncdservice.exameyel=2 then 'Mild NPDR'
when ncdservice.exameyel=3 then 'Moderate NPDR'
when ncdservice.exameyel=4 then 'Severe NPDR'
when ncdservice.exameyel=5 then 'CSME'
when ncdservice.exameyel=6 then 'PDR' else ' x ' end as ผลการตรวจจอประสาทตาด้านซ้าย
,case when ncdservice.exameyer=1 then 'NO DR'
when ncdservice.exameyer=2 then 'Mild NPDR'
when ncdservice.exameyer=3 then 'Moderate NPDR'
when ncdservice.exameyer=4 then 'Severe NPDR'
when ncdservice.exameyer=5 then 'CSME'
when ncdservice.exameyer=6 then 'PDR' else ' x ' end as ผลการตรวจจอประสาทตาด้านขวา
,case when ncdservice.eyeopthalmoscope in (1,3) then 'opthalmoscope'
when ncdservice.eyeopthalmoscope in (2,4) then 'fundus camera' else ' x ' end as วิธีตรวจจอประสาทตา
,case when ncdservice.eyeopthalmoscope in (1,2) then 'ปกติ'
when ncdservice.eyeopthalmoscope in (3,4) then 'ผิดปกติ' else ' x ' end as ผลการตรวจจอประสาทตา

FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
INNER JOIN house ON person.hcode = house.hcode
INNER JOIN personchronic ON person.pcucodeperson = personchronic.pcucodeperson AND person.pid = personchronic.pid
INNER JOIN ncdservice ON personchronic.pid = ncdservice.pid

where personchronic.chroniccode BETWEEN 'E11' and 'E14'
and ncdservice.date_serv between '20141001' and '20150930'
and (ncdservice.examfoot is not null or ncdservice.eyeopthalmoscope !=9)
and person.typelive IN (0,1,3)
GROUP BY person.pid
order by ncdservice.date_serv;