query ระยะเวลา รอคอย OPD

SELECT
    LEFT(o.vstdate, 7) AS ym, -- ปีและเดือนของการ Visit (YYYY-MM)
    COUNT(DISTINCT o.vn) AS total_visits, -- นับจำนวน Visit (VN) ที่ไม่ซ้ำกัน
    TIME_FORMAT(
        SEC_TO_TIME(
            AVG(
                TIME_TO_SEC(
                    TIMEDIFF(
                        CONCAT(p.rxdate, ' ', p.rxtime), -- เวลาแพทย์สั่งยา/บริการ
                        CONCAT(o.vstdate, ' ', o.vsttime)  -- เวลาลงทะเบียน
                    )
                )
            )
        ),
        '%T'
    ) AS avg_visit_time_hh_mm_ss, -- ค่าเฉลี่ยของระยะเวลา Visit (HH:MM:SS)
    CONCAT(
        FLOOR(
            TIME_FORMAT(
                SEC_TO_TIME(
                    AVG(
                        TIME_TO_SEC(
                            TIMEDIFF(
                                CONCAT(p.rxdate, ' ', p.rxtime),
                                CONCAT(o.vstdate, ' ', o.vsttime)
                            )
                        )
                    )
                ),
                '%H'
            ) * 60 + TIME_FORMAT(
                SEC_TO_TIME(
                    AVG(
                        TIME_TO_SEC(
                            TIMEDIFF(
                                CONCAT(p.rxdate, ' ', p.rxtime),
                                CONCAT(o.vstdate, ' ', o.vsttime)
                            )
                        )
                    )
                ),
                '%i'
            )
        ),
        ':',
        TIME_FORMAT(
            SEC_TO_TIME(
                AVG(
                    TIME_TO_SEC(
                        TIMEDIFF(
                            CONCAT(p.rxdate, ' ', p.rxtime),
                            CONCAT(o.vstdate, ' ', o.vsttime)
                        )
                    )
                )
            ),
            '%s'
        )
    ) AS avg_visit_time_min_sec -- ค่าเฉลี่ยของระยะเวลา Visit ในรูปแบบ นาที:วินาที
FROM
    ovst o
LEFT JOIN
    opitemrece p ON o.vn = p.vn AND p.rxdate = o.vstdate
LEFT JOIN
    xray_head xr ON o.vn = xr.vn
LEFT JOIN
    lab_head lb ON o.vn = lb.vn
LEFT JOIN
    ovstoprt op ON o.vn = op.vn
LEFT JOIN
    ipt i ON o.vn = i.vn
LEFT JOIN
    opitemrece ekg ON o.vn = ekg.vn AND ekg.item_type = 'EKG' -- สมมติว่า EKG มี item_type เป็น 'EKG'
WHERE
    TIMEDIFF(CONCAT(p.rxdate, ' ', p.rxtime), CONCAT(o.vstdate, ' ', o.vsttime)) < '04:00:00'
    AND o.vstdate BETWEEN '2025-09-01' AND '2025-09-31'
    AND xr.vn IS NULL
    AND lb.vn IS NULL
    AND i.vn IS NULL
    AND ekg.vn IS NULL
    AND o.vsttime BETWEEN '07:00:00' AND '16:30:00'
    AND o.main_dep IN ('001','057','058','066','072','073','075','076','077','095','096','100','101','102','103','104','105')
GROUP BY
    ym
ORDER BY
    ym

SELECT
    LEFT(o.vstdate, 7) AS ym,
    COUNT(DISTINCT o.vn) AS total_visits,
    TIME_FORMAT(
        SEC_TO_TIME(
            AVG(
                TIME_TO_SEC(
                    TIMEDIFF(
                        CONCAT(p.rxdate, ' ', p.rxtime),
                        CONCAT(o.vstdate, ' ', o.vsttime)
                    )
                )
            )
        ),
        '%T'
    ) AS avg_visit_time_hh_mm_ss,
    -- ส่วนคำนวณ นาที:วินาที ที่กระชับขึ้น
    CONCAT(
        FLOOR(AVG(TIME_TO_SEC(TIMEDIFF(CONCAT(p.rxdate, ' ', p.rxtime), CONCAT(o.vstdate, ' ', o.vsttime)))) / 60),
        ':',
        LPAD(FLOOR(AVG(TIME_TO_SEC(TIMEDIFF(CONCAT(p.rxdate, ' ', p.rxtime), CONCAT(o.vstdate, ' ', o.vsttime)))) % 60), 2, '0')
    ) AS avg_visit_time_min_sec
FROM
    ovst o
INNER JOIN -- ใช้ INNER JOIN กับ opitemrece เพื่อให้มั่นใจว่ามีรายการสั่งการ
    opitemrece p ON o.vn = p.vn AND p.rxdate = o.vstdate
LEFT JOIN
    xray_head xr ON o.vn = xr.vn
LEFT JOIN
    lab_head lb ON o.vn = lb.vn
LEFT JOIN
    -- ค้นหา EKG จาก opitemrece (ตรวจสอบรหัส icode ของ EKG ในระบบคุณอีกทีนะครับ)
    opitemrece ekg ON o.vn = ekg.vn AND ekg.icode IN ('3000159') -- แก้เป็นรหัส EKG ของโรงพยาบาล
WHERE
    o.vstdate BETWEEN '2025-10-01' AND '2025-10-31'
    AND o.vsttime BETWEEN '07:00:00' AND '16:30:00'
    AND o.main_dep IN ('001','057','058','066','072','073','075','076','077','095','096','100','101','102','103','104','105')
    -- เงื่อนไขสำคัญ: ต้องมีอย่างใดอย่างหนึ่ง (ใช้ OR)
    AND (xr.vn IS NOT NULL OR lb.vn IS NOT NULL OR ekg.vn IS NOT NULL)
    -- กรองค่าผิดปกติ (เช่น ระยะเวลาติดลบ หรือนานเกินไป)
    AND TIMEDIFF(CONCAT(p.rxdate, ' ', p.rxtime), CONCAT(o.vstdate, ' ', o.vsttime)) BETWEEN '00:00:01' AND '04:00:00'
GROUP BY
    ym
ORDER BY
    ym;