พี่หยุย

SELECT
 
v.date_visit, p.hn, p.cid, concat(p.prename,p.name,' ',p.surname) as 'ชื่อ-สกุล'
,TRUNCATE ((SELECT DATEDIFF(v.date_visit, STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) as 'อายุ ณ วันวินิจฉัย'
,(SELECT GROUP_CONCAT(DISTINCT(d.icd10)) FROM hos_doctor_diagnosis d WHERE d.visit_code = v.visit_code and d.dxtype = 1) as 'diag หลัก'
,(SELECT GROUP_CONCAT(DISTINCT(d.icd10)) FROM hos_doctor_diagnosis d WHERE d.visit_code = v.visit_code and d.dxtype != 1) as 'diag ร่วม'
,GROUP_CONCAT(DISTINCT(pc.icd9)) AS 'ICD9'
,GROUP_CONCAT(DISTINCT(t.drug)) AS 'Drug'
 
FROM hos_pt_visit v
INNER JOIN hos_pt p ON v.hn = p.hn
INNER JOIN hos_doctor_diagnosis d ON v.visit_code = d.visit_code
LEFT JOIN hos_doctor_procedure pc on v.visit_code = pc.visit_code
LEFT JOIN hos_doctor_treatment t ON v.visit_code = t.visit_code
WHERE v.date_visit_sql BETWEEN '20221001' AND '20240915'
and d.icd10 IN ('S4200','S5250','S5280','S6250','S6260','S6270','S9240','S9250')
GROUP BY v.visit_code