SELECT
person.pid AS 'HN',
CONCAT(ctitle.titlename,person.fname,' ',person.lname) AS 'ชื่อ - สกุล',
person.idcard 'เลขบัตร ปชช',
house.hno AS 'บ้านเลขที่',
village.villno AS 'หมู่ที่',
person.rightcode AS 'สิทธิ์การรักษา',
personchronic.cup AS 'สถานที่รับบริการ',
max(visitlabchcyhembmsse.datecheck) AS 'วันที่ตรวจ',
MAX(CASE WHEN visitlabchcyhembmsse.datecheck BETWEEN '2013-04-01' AND '2014-03-30' THEN visitlabchcyhembmsse.labresultdigit ELSE NULL END) AS 'HbA1c'
FROM
person
INNER JOIN personchronic ON personchronic.pcucodeperson = person.pcucodeperson AND personchronic.pid = person.pid
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN visitlabchcyhembmsse ON personchronic.pcucodeperson = visitlabchcyhembmsse.pcucodeperson AND personchronic.pid = visitlabchcyhembmsse.pid
INNER JOIN cright ON person.rightcode = cright.rightcode
INNER JOIN crightgroup ON cright.rightgroup = crightgroup.rightgroupcode
WHERE
visitlabchcyhembmsse.labcode = 'CH99'
AND person.pcucodeperson = personchronic.cup
and personchronic.chroniccode BETWEEN 'e10' AND 'e14'
and concat(person.pid,person.pcucodeperson) not in (select concat(persondeath.pid,persondeath.pid) from persondeath)
and person.typelive IN ('1', '3')
and person.dischargetype = '9'
AND crightgroup.rightgroupcode IN (3,4)
GROUP BY
person.pid
ORDER BY
village.villno;