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
,personchronic.chroniccode,
ncdservice.date_serv as วันที่ตรวจ
,case when ncdservice.examfoot = 0 then 'Normal' when ncdservice.examfoot = 1 then 'Low Risk' when ncdservice.examfoot = 2 then 'Modurate Risk'
when ncdservice.examfoot = 3 then 'Hight Rist' when ncdservice.examfoot = 4 then 'Foot Ulcer' when ncdservice.examfoot = 5 then 'Amputation' else '' end 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 'ไม่ได้ตรวจ' 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 'ไม่ได้ตรวจ' end as ผลการตรวจจอประสาทตาด้านขวา
,case when ncdservice.eyeopthalmoscope in (1,3) then 'opthalmoscope' when ncdservice.eyeopthalmoscope in (2,4) then 'fundus camera' else '' end as วิธีตรวจจอประสาทตา
,case when ncdservice.eyeopthalmoscope in (1,2) then 'ปกติ' when ncdservice.eyeopthalmoscope in (3,4) then 'ผิดปกติ' else '' end as ผลการตรวจจอประสาทตา
FROM
person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
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.9'
and ncdservice.date_serv between '2013-10-01' and CURDATE()
and (ncdservice.examfoot is not null or ncdservice.eyeopthalmoscope !=9)
order by ncdservice.date_serv;