query แพ้ยา

SET @startdate = '2025-01-01';
SET @enddate = '2025-08-30';
SELECT
 p.pname,
 p.fname,
 p.lname,
 a.an,
 a.hn,
 a.vstdate,
 v.pdx,
 i.tname,
 o.agent
FROM
 ovst a
 LEFT JOIN patient p ON p.hn = a.hn
 LEFT JOIN opd_allergy o ON o.hn = a.hn
 LEFT JOIN vn_stat v ON v.hn = a.hn
 LEFT JOIN icd101 i ON i.`code` = v.pdx
WHERE
 a.vstdate BETWEEN @startdate
 AND @enddate
 AND o.agent IS NOT NULL
 AND o.agent <> ''
--  AND o.agent LIKE '%adr%'
GROUP BY
 a.hn

SELECT
    p.hn AS "HN",
    CONCAT(p.pname, p.fname, ' ', p.lname) AS "fullname",
    TIMESTAMPDIFF(YEAR, p.birthday, CURDATE()) AS "อายุ",
    o.agent AS "ยาที่แพ้",
    o.symptom AS "อาการที่แพ้",
 
    n.naranjo_result_name AS "ผล Naranjo",
    DATE_FORMAT(o.report_date, '%d/%m/%Y') AS "วันที่มีรายงาน",
    DATE_FORMAT(o.begin_date, '%d/%m/%Y') AS "วันที่มีอาการ"
    ,u.name AS "ผู้รายงาน"
    ,se.seiousness_name AS 'ความร้ายแรง'
    ,al_type.opd_allergy_type_name AS 'ชนิดการแพ้'
    ,alert_type.opd_allergy_alert_type_name AS 'การเตือน'
    ,al_group.allergy_group_name AS 'สาเหตุการเกิด'
    ,al_source.opd_allergy_source_name AS 'แหล่งข้อมูล'
    ,al_relation.relation_name AS 'ระดับความสัมพันธ์'
    ,result.result_name AS 'ผลที่เกิดขึ้นภายหลัง'
    ,o.department AS 'ประเภทผู้ป่วย'
    ,o.ward AS 'หอผู้ป่วย'
    ,dep.department AS 'ห้อง'
    ,al_report.opd_allergy_report_type_name AS 'ประเภทรายงาน'
    ,adr_dialog.adr_consult_dialog_name AS 'Consult dialog'
    ,al_sym.opd_allergy_symtom_type_name AS 'Symptom'
    ,o.dosage_text AS 'ขนาด'
    ,o.usage_text AS 'วิธีใช้'
    ,o.lab_text AS 'Lab'
FROM
    opd_allergy o
 
LEFT JOIN patient p ON o.hn = p.hn
LEFT JOIN naranjo_result n ON o.naranjo_result_id = n.naranjo_result_id
LEFT JOIN opduser u ON o.reporter = u.loginname
LEFT JOIN allergy_seriousness se ON se.seriousness_id = o.seriousness_id
LEFT JOIN allergy_result result ON result.allergy_result_id = o.allergy_result_id
LEFT JOIN opd_allergy_type al_type ON al_type.opd_allergy_type_id = o.opd_allergy_type_id
LEFT JOIN opd_allergy_alert_type alert_type ON alert_type.opd_allergy_alert_type_id = o.opd_allergy_alert_type_id
LEFT JOIN allergy_group al_group ON al_group.allergy_group_id = o.allergy_group_id
LEFT JOIN opd_allergy_source al_source ON al_source.opd_allergy_source_id = o.opd_allergy_source_id
LEFT JOIN allergy_relation al_relation ON al_relation.allergy_relation_id = o.allergy_relation_id
LEFT JOIN kskdepartment dep ON dep.depcode = o.depcode
LEFT JOIN opd_allergy_report_type al_report ON o.opd_allergy_report_type_id = al_report.opd_allergy_report_type_id
LEFT JOIN adr_consult_dialog adr_dialog ON adr_dialog.adr_consult_dialog_id = o.adr_consult_dialog_id
LEFT JOIN opd_allergy_symptom_type al_sym ON al_sym.opd_allergy_symptom_type_id = o.opd_allergy_symtom_type_id
 
WHERE o.report_date >= '2025-03-01'
AND o.hn = '430018427'
 
order by fullname, o.report_date

SELECT
p.hn AS "HN"
,CONCAT(p.pname, p.fname, ' ', p.lname) AS "ชื่อ-สกุลผู้ป่วย"
,a.agent
,b.naranjo_algorithm_name,a.naranjo_score
,c.naranjo_check_name
FROM opd_allergy_naranjo a
INNER JOIN naranjo_algorithm b ON a.naranjo_algorithm_id = b.naranjo_algorithm_id
LEFT JOIN patient p ON p.hn = a.hn
LEFT JOIN naranjo_check c ON c.naranjo_check_id = a.naranjo_check_id

SELECT
    oa.hn AS "HN",
    p.fname,
    p.lname,
    oa.agent AS "ยาที่แพ้",
    (SELECT SUM(naranjo_score) FROM opd_allergy_naranjo WHERE hn = oa.hn) AS "คะแนน Naranjo รวม",
    nr.naranjo_result_name AS "ผลการประเมิน (ที่บันทึกไว้)",
    DATE_FORMAT(oa.report_date, '%d/%m/%Y') AS "วันที่รายงาน"
FROM
    opd_allergy oa
JOIN
    patient p ON oa.hn = p.hn
INNER JOIN naranjo_result nr ON oa.naranjo_result_id = nr.naranjo_result_id
-- WHERE
--     oa.hn = '460007227'
GROUP BY
    oa.opd_allergy_id
ORDER BY
    oa.hn, oa.report_date