ทะเบียนผู้สูงอายุได้รับการคัดกรอง 9 ด้าน

คำสั่ง ทะเบียนผู้สูงอายุได้รับการคัดกรอง 9 ด้าน

SELECT p.patient_hn,p.fname,p.cid,p.birthdate,p.age_y,p.address,p.village_moo,pp.e1, pp.e2, pp.e3, pp.e4, pp.e5,pp.urine, pp.ear, pp.adl, pp.amt,pp.`2q`, pp.fall, pp.mount, pp.nutri
FROM ( SELECT p.patient_hn,
CONCAT(p.pname, p.fname, ' ', p.lname) AS fname,p.cid,p.birthdate,
TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) AS age_y,h.address,v.village_id,v.village_moo
FROM person p
LEFT JOIN house h ON p.house_id = h.house_id LEFT JOIN village v ON h.village_id = v.village_id
WHERE p.house_regist_type_id IN ('1','3')
AND p.person_discharge_id = '9' AND p.nationality = '99' AND TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) >= 60
) p
LEFT JOIN ( SELECT pp.hn,GROUP_CONCAT(pp.e1) AS e1,GROUP_CONCAT(pp.e2) AS e2,GROUP_CONCAT(pp.e3) AS e3,GROUP_CONCAT(pp.e4) AS e4,GROUP_CONCAT(pp.e5) AS e5,GROUP_CONCAT(pp.urine) AS urine,GROUP_CONCAT(pp.ear) AS ear,
GROUP_CONCAT(pp.adl) AS adl,GROUP_CONCAT(pp.amt) AS amt,GROUP_CONCAT(pp.`2q`) AS `2q`,GROUP_CONCAT(pp.fall) AS fall,GROUP_CONCAT(pp.mount) AS mount,GROUP_CONCAT(pp.nutri) AS nutri
FROM ( SELECT
pp.hn,
IF(t.pp_special_code IN ('1B1240','1B1241','1B1242'), t.pp_special_code, NULL) AS e1, IF(t.pp_special_code IN ('1B1243','1B1244','1B1245'), t.pp_special_code, NULL) AS e2,
IF(t.pp_special_code IN ('1B1250','1B1251','1B1252'), t.pp_special_code, NULL) AS e3, IF(t.pp_special_code IN ('1B1253','1B1254','1B1255'), t.pp_special_code, NULL) AS e4,
IF(t.pp_special_code IN ('1B1256','1B1257','1B1258'), t.pp_special_code, NULL) AS e5, IF(t.pp_special_code IN ('1B1283','1B1284','1B1285'), t.pp_special_code, NULL) AS urine,
IF(t.pp_special_code IN ('1B1140','1B1141','1B1142','1B1143','1B1144'), t.pp_special_code, NULL) AS ear, IF(t.pp_special_code IN ('1B1280','1B1281','1B1282'), t.pp_special_code, NULL) AS adl,
IF(t.pp_special_code IN ('1B1220','1B1221','1B1223'), t.pp_special_code, NULL) AS amt, IF(t.pp_special_code IN ('1B0280','1B0281'), t.pp_special_code, NULL) AS `2q`,
IF(t.pp_special_code IN ('1B1200','1B1201','1B1202','1B1203'), t.pp_special_code, NULL) AS fall, IF(t.pp_special_code IN ('1B1260','1B1261','1B1262','1B1263','1B1264'), t.pp_special_code, NULL) AS mount,
IF(t.pp_special_code IN ('1K10','1K11','1K12','1K13','1K14','1K15','1K16'), t.pp_special_code, NULL) AS nutri
FROM pp_special pp INNER JOIN pp_special_type t ON pp.pp_special_type_id = t.pp_special_type_id
WHERE
t.pp_special_code IN (
'1B1240','1B1241','1B1242','1B1243','1B1244','1B1245','1B1250','1B1251','1B1252','1B1253','1B1254','1B1255',
'1B1256','1B1257','1B1258','1B1283','1B1284','1B1285','1B1140','1B1141','1B1142','1B1143','1B1144',
'1B1280','1B1281','1B1282','1B1220','1B1221','1B1223','1B0280','1B0281','1B1200','1B1201','1B1202','1B1203',
'1B1260','1B1261','1B1262','1B1263','1B1264','1K10','1K11','1K12','1K13','1K14','1K15','1K16'
)
AND pp.entry_datetime BETWEEN '2025-10-01' AND '2026-09-30'
) pp
GROUP BY pp.hn
) pp ON p.patient_hn = pp.hn
ORDER BY p.village_id, p.address;