SELECT
person.pid,concat(titlename,fname,' ',lname) as name,
person.idcard
,getAgeYearNum(birth,current_date) as age,
CASE WHEN person.candobedhomesocial = '1' THEN 1 ELSE '' END AS 'ติดสังคม',
CASE WHEN person.candobedhomesocial = '2' THEN 1 ELSE '' END AS 'ติดบ้าน',
CASE WHEN person.candobedhomesocial = '3' THEN 1 ELSE '' END AS 'ติดเตียง',
house.hno as 'เลขที่',
village.villno as 'หมู่',
villname,
sum(case when (personunable1type.typecode) IN (101,1111,1112,1121,1122) THEN 1 ELSE '' END) AS 'การมองเห็น'
,sum(case when (personunable1type.typecode) IN (103,109,1231,1321,1331,1332,1333,1334,1341,1342,1343,1344,1351,1352,1353,1354,1355,1361,1362,1363,1364,1365,1371,1372,1381,1382,1383,1384,1385,1391,1392,1393,1394,1395,1396) THEN 1 ELSE '' END) AS 'ทางกาย/การเคลื่อนไหว'
,sum(case when (personunable1type.typecode) IN (102,1211,1212,1221,1222,1311,1312,1313
) THEN 1 ELSE '' END) AS 'การได้ยิน'
,sum(case when (personunable1type.typecode) IN (104) THEN 1 ELSE '' END) AS 'จิตใจพฤติกรรม'
,sum(case when (personunable1type.typecode) IN (105,106) AND getagemonth(person.birth,'2015-10-01') BETWEEN 30 AND 215 THEN 1 ELSE '' END) AS 'สติปัญญา 2.6 -17'
,sum(case when (personunable1type.typecode) IN (105,106) AND getagemonth(person.birth,'2015-10-01') >= 216 THEN 1 ELSE '' END) AS 'การเรียนรู้อายุ 18 ปีขึ้นไป'
,sum(case when (personunable1type.typecode) IN (107) THEN 1 ELSE '' END) AS 'ออทิสติก'
from
person
INNER JOIN ctitle ON person.prename = ctitle.titlecode
RIGHT JOIN personunable ON person.pid = personunable.pid
LEFT JOIN personunable1type ON person.pid = personunable1type.pid
INNER JOIN house on person.pcucodeperson = house.pcucodeperson and person.hcode = house.hcode
INNER JOIN village on house.villcode = village.villcode and villno <>'00'
LEFT JOIN persondeath ON person.pid = persondeath.pid
WHERE person.typelive in ('1','3') 
AND persondeath.pid IS NULL
AND person.dischargetype<>'1'
GROUP BY villno,hno,person.pid;