query ฟ้า แผนไทย

  1. Overview
  2. HosXP
  3. Query
  4. query ฟ้า แผนไทย

Query

SELECT
o.hn,
IFNULL(CONCAT(p.pname, p.fname, ' ', p.lname), '') AS ptname,
GROUP_CONCAT(DISTINCT d_main.icd10 ORDER BY d_main.icd10 ASC SEPARATOR ', ') AS main_diagnoses,
GROUP_CONCAT(DISTINCT d_sub.icd10 ORDER BY d_sub.icd10 ASC SEPARATOR ', ') AS sub_diagnoses,
COUNT(o.vn) AS total_visits_per_person,
GROUP_CONCAT(o.vstdate ORDER BY o.vstdate ASC SEPARATOR ', ') AS all_visit_dates
FROM
ovst o
INNER JOIN
ovstdiag d_main ON o.vn = d_main.vn AND d_main.diagtype = '1' AND d_main.icd10 BETWEEN 'I60' AND 'I69'
INNER JOIN
ovstdiag d_sub ON o.vn = d_sub.vn AND d_sub.diagtype = '2' AND d_sub.icd10 BETWEEN 'U610' AND 'U6118'
LEFT JOIN
patient p ON o.hn = p.hn
WHERE
o.vstdate BETWEEN '2024-10-01' AND '2025-09-30'
GROUP BY
o.hn, p.pname, p.fname, p.lname, p.sex, p.birthday
ORDER BY
total_visits_per_person DESC, o.hn ASC