query ตาม Diag

OPD

SELECT
o.hn,
IFNULL(CONCAT(p.pname, p.fname, ' ', p.lname), '') AS ptname,
p.sex,
p.birthday,
FLOOR(DATEDIFF(MIN(o.vstdate), p.birthday) / 365.25) AS age_at_first_relevant_visit,
COUNT(o.vn) AS total_visits_per_person,
GROUP_CONCAT(DISTINCT d_dm.icd10 ORDER BY d_dm.icd10 ASC SEPARATOR ', ') AS dm_diagnoses,
GROUP_CONCAT(DISTINCT d_ckd.icd10 ORDER BY d_ckd.icd10 ASC SEPARATOR ', ') AS ckd_diagnoses,
GROUP_CONCAT(DISTINCT dr.name ORDER BY dr.name ASC SEPARATOR ' ; ') AS received_medications_for_person
FROM
ovst o
INNER JOIN
ovstdiag d_dm ON o.vn = d_dm.vn AND d_dm.icd10 BETWEEN 'E10' AND 'E14' AND d_dm.diagtype IN ('1', '2')
INNER JOIN
ovstdiag d_ckd ON o.vn = d_ckd.vn AND d_ckd.icd10 IN ('N184', 'N185', 'N186') AND d_ckd.diagtype IN ('1', '2')
LEFT JOIN
patient p ON o.hn = p.hn
LEFT JOIN
opitemrece opir ON o.vn = opir.vn
LEFT JOIN
drugitems dr ON opir.icode = dr.icode AND opir.item_type = 'D'
WHERE
o.vstdate BETWEEN '2023-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

IPD

SELECT
i.hn,
i.an,
i.regdate AS date_visit,
IFNULL(CONCAT(p.pname, p.fname, ' ', p.lname), '') AS ptname,
p.sex,
p.birthday,
FLOOR(DATEDIFF(i.regdate, p.birthday) / 365.25) AS age_years,
d.icd10,
IFNULL(icd.name, '') AS icd10_name,
'IPD' AS patient_type,
i.ward AS ward_code,
w.name AS ward_name
FROM
ipt i
INNER JOIN
iptdiag d ON i.an = d.an AND d.diagtype IN ('1', '2')
LEFT JOIN
patient p ON i.hn = p.hn
LEFT JOIN
icd101 icd ON d.icd10 = icd.code
LEFT JOIN
ward w ON i.ward = w.ward
WHERE
i.regdate BETWEEN '2024-10-01' AND '2025-09-30'
AND (
d.icd10 LIKE 'J12%' OR
d.icd10 = 'J13' OR
d.icd10 = 'J14' OR
d.icd10 LIKE 'J15%' OR
d.icd10 LIKE 'J16%' OR
d.icd10 LIKE 'J17%' OR
d.icd10 = 'J18.9'
)
ORDER BY
w.name ASC, i.regdate ASC, i.hn ASC

Phlebitis OPD

SELECT
o.hn,
o.vn,
o.vstdate AS date_visit,
IFNULL(CONCAT(p.pname, p.fname, ' ', p.lname), '') AS ptname,
p.sex,
p.birthday,
FLOOR(DATEDIFF(o.vstdate, p.birthday) / 365.25) AS age_years,
d.icd10,
IFNULL(icd.name, '') AS icd10_name,
'OPD' AS patient_type
FROM
ovst o
INNER JOIN
ovstdiag d ON o.vn = d.vn AND d.diagtype IN ('1', '2')
LEFT JOIN
patient p ON o.hn = p.hn
LEFT JOIN
icd101 icd ON d.icd10 = icd.code
WHERE
o.vstdate BETWEEN '2024-10-01' AND '2025-09-30'
AND d.icd10 LIKE 'I80%'
ORDER BY
o.vstdate ASC, o.hn ASC

Phlebitis IPD

SELECT
i.hn,
i.an,
i.regdate AS date_visit,
IFNULL(CONCAT(p.pname, p.fname, ' ', p.lname), '') AS ptname,
p.sex,
p.birthday,
FLOOR(DATEDIFF(i.regdate, p.birthday) / 365.25) AS age_years,
d.icd10,
IFNULL(icd.name, '') AS icd10_name,
'IPD' AS patient_type,
i.ward AS ward_code,
w.name AS ward_name
FROM
ipt i
INNER JOIN
iptdiag d ON i.an = d.an AND d.diagtype IN ('1', '2')
LEFT JOIN
patient p ON i.hn = p.hn
LEFT JOIN
icd101 icd ON d.icd10 = icd.code
LEFT JOIN
ward w ON i.ward = w.ward
WHERE
i.regdate BETWEEN '2024-10-01' AND '2025-09-30'
AND d.icd10 LIKE 'I80%'
ORDER BY
w.name ASC, i.regdate ASC, i.hn ASC