query xray chest จาก icd10

SET @startdate = '2025-01-01';
SET @enddate   = CURDATE();
 
SELECT
o.hn,
p.cid,
  o.vn AS visit_code,
  CONCAT(p.pname, ' ', p.fname, ' ', p.lname) AS `name`,
  o.vstdate AS date_visit,
  GROUP_CONCAT(DISTINCT d.icd10 ORDER BY d.icd10 SEPARATOR ', ') AS icd10_list,
  GROUP_CONCAT(DISTINCT ni.icode ORDER BY ni.icode SEPARATOR ', ') AS xray_code_list,
  GROUP_CONCAT(DISTINCT ni.name ORDER BY ni.name SEPARATOR ', ') AS xray_name_list,
  CONCAT(
    COALESCE(p.addrpart, ''), ' | ',
    'หมู่ ', COALESCE(p.moopart, ''), ' | ',
    'ต. ', COALESCE(t3.name, ''), ' | ',
    'อ. ', COALESCE(t2.name, ''), ' | ',
    'จ. ', COALESCE(t1.name, '')
  ) AS full_address
 
FROM ovst o
JOIN patient p ON p.hn = o.hn
JOIN ovstdiag d ON d.vn = o.vn
INNER JOIN opitemrece opir ON opir.vn = o.vn
LEFT JOIN nondrugitems ni ON ni.icode = opir.icode
LEFT JOIN thaiaddress t1 ON t1.chwpart = p.chwpart AND t1.amppart = '00' AND t1.tmbpart = '00'
LEFT JOIN thaiaddress t2 ON t2.chwpart = p.chwpart AND t2.amppart = p.amppart AND t2.tmbpart = '00'
LEFT JOIN thaiaddress t3 ON t3.chwpart = p.chwpart AND t3.amppart = p.amppart AND t3.tmbpart = p.tmbpart
 
WHERE o.vstdate BETWEEN @startdate AND @enddate
  AND (
        d.icd10 IN ('J44','J440','J441','J449','B20','B21','B22','B23','B24')
     OR d.icd10 REGEXP '^N18[0-9]?$'
     OR d.icd10 REGEXP '^F10[0-9]?$'
     OR d.icd10 REGEXP '^F11[0-9]?$'
     OR d.icd10 REGEXP '^F12[0-9]?$'
     OR d.icd10 REGEXP '^F14[0-9]?$'
     OR d.icd10 REGEXP '^F15[0-9]?$'
     OR d.icd10 REGEXP '^F16[0-9]?$'
     OR d.icd10 REGEXP '^F17[0-9]?$'
     OR d.icd10 REGEXP '^F18[0-9]?$'
     OR d.icd10 REGEXP '^F19[0-9]?$'
  )
  AND (ni.name LIKE '%CHEST%' OR opir.icode LIKE '%CHEST%')
GROUP BY o.vn, o.vstdate
ORDER BY o.vstdate