ไฟล์ เวลามารับบริการเฉลี่ย (ระยะเวลารอคอย) OPD ปีงบ64-67

SELECT
v.doctor_room,r.name,COUNT(DISTINCT v.visit_code) as 'visit'
FROM hos_pt_visit v
INNER JOIN hos_code_doctor_room r ON v.doctor_room = r.code
WHERE v.date_visit_sql BETWEEN '20250201' AND '20250228'
GROUP BY v.doctor_room

SET @month_start = '20241201';
SET @month_end = '20241231';
 
-- Adjust @start_date usage for clarity
SET @start_date_prefix = '202412';
 
SELECT
    AVG(
        TIMESTAMPDIFF(
            MINUTE,
            CASE
                WHEN SUBSTRING(vt.index_start, 9, 6) < '080000' THEN
                    CONCAT(LEFT(vt.index_start, 8), '080000')
                ELSE
                    vt.index_start
            END,
            vt.doctor_end
        ) + 10
    ) AS `minutes_with_medication`
FROM
    hos_pt_visit v
INNER JOIN
    hos_pt_visit_time vt
    ON v.visit_code = vt.visit_code
LEFT JOIN
    hos_lab lab
    ON vt.visit_code = lab.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    hos_xray xray
    ON vt.visit_code = xray.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    hos_doctor_procedure pc
    ON vt.visit_code = pc.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    (SELECT v.visit_code
     FROM hos_pt_visit v
     INNER JOIN hos_doctor_procedure pc ON v.visit_code = pc.visit_code
     WHERE v.date_visit_sql BETWEEN @month_start AND @month_end
       AND pc.icd9 = '8952') excluded
    ON vt.visit_code = excluded.visit_code
WHERE
    v.date_visit_sql BETWEEN @month_start AND @month_end
    AND v.doctor_room = '1'
    AND vt.doctor_end IS NOT NULL
    AND lab.visit_code IS NULL
    AND xray.visit_code IS NULL
    AND pc.visit_code IS NULL
    AND excluded.visit_code IS NULL
    AND (TIMESTAMPDIFF(MINUTE, vt.index_start, vt.doctor_end) + 10) <= 360

SET @month_start = '20250201';
SET @month_end = '20250228';
 
SET @start_date_prefix = '202502';
 
SELECT
COUNT(DISTINCT v.visit_code) AS 'visit'
FROM
    hos_pt_visit v
INNER JOIN
    hos_pt_visit_time vt
    ON v.visit_code = vt.visit_code
LEFT JOIN
    hos_lab lab
    ON vt.visit_code = lab.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    hos_xray xray
    ON vt.visit_code = xray.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    hos_doctor_procedure pc
    ON vt.visit_code = pc.visit_code AND vt.index_start LIKE CONCAT(@start_date_prefix, '%')
LEFT JOIN
    (SELECT v.visit_code
     FROM hos_pt_visit v
     INNER JOIN hos_doctor_procedure pc ON v.visit_code = pc.visit_code
     WHERE v.date_visit_sql BETWEEN @month_start AND @month_end
       AND pc.icd9 = '8952') excluded
    ON vt.visit_code = excluded.visit_code
WHERE
    v.date_visit_sql BETWEEN @month_start AND @month_end
    AND v.doctor_room = '1'
    AND vt.doctor_end IS NOT NULL
    AND lab.visit_code IS NULL
    AND xray.visit_code IS NULL
    AND pc.visit_code IS NULL
    AND excluded.visit_code IS NULL
    AND (TIMESTAMPDIFF(MINUTE, vt.index_start, vt.doctor_end) + 10) <= 360

SET @month_start = '20250101';
SET @month_end = '20250131';
SET @start_date = '202501%';
 
select
AVG((TIMESTAMPDIFF(MINUTE,
    IF(
        SUBSTRING(vt.index_start, 9, 6) < '080000',
        CONCAT(
            LEFT(vt.index_start, 8), -- Preserve the date part
            '080000' -- Change the time part to 080000
        ),
        vt.index_start -- Keep the original value if it's not less than 080000
    )
,vt.doctor_end) + 10)) as 'นาที (10 นาที จัดยา)'
from hos_pt_visit v
inner join hos_pt_visit_time vt on v.visit_code = vt.visit_code
where v.date_visit_sql between @month_start and @month_end
and v.doctor_room = '1'
and vt.doctor_end IS NOT null
and (TIMESTAMPDIFF(MINUTE,vt.index_start,vt.doctor_end)+10) <= 360
AND v.visit_code NOT IN (
SELECT
v.visit_code
from hos_pt_visit v
inner join hos_pt_visit_time vt on v.visit_code = vt.visit_code
where v.date_visit_sql between @month_start and @month_end
and v.doctor_room = '1'
and vt.doctor_end IS NOT null
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_lab lab on vt.visit_code = lab.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_xray xray on vt.visit_code = xray.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_doctor_procedure pc on vt.visit_code = pc.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
SELECT
        distinct(v.visit_code)
    FROM
        hos_pt_visit v
    inner join hos_doctor_procedure pc on v.visit_code = pc.visit_code
WHERE
v.date_visit_sql BETWEEN @start_date AND @end_date
AND pc.icd9 = '8952'
)
and (TIMESTAMPDIFF(MINUTE,vt.index_start,vt.doctor_end)+10) <= 360
)

SET @month_start = '20250201';
SET @month_end = '20250228';
SET @start_date = '202502%';
 
select
COUNT(DISTINCT v.visit_code) AS 'visit'
from hos_pt_visit v
inner join hos_pt_visit_time vt on v.visit_code = vt.visit_code
where v.date_visit_sql between @month_start and @month_end
and v.doctor_room = '1'
and vt.doctor_end IS NOT null
and (TIMESTAMPDIFF(MINUTE,vt.index_start,vt.doctor_end)+10) <= 360
AND v.visit_code NOT IN (
SELECT
v.visit_code
from hos_pt_visit v
inner join hos_pt_visit_time vt on v.visit_code = vt.visit_code
where v.date_visit_sql between @month_start and @month_end
and v.doctor_room = '1'
and vt.doctor_end IS NOT null
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_lab lab on vt.visit_code = lab.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_xray xray on vt.visit_code = xray.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
    select distinct(vt.visit_code) from hos_pt_visit_time vt
    inner join hos_doctor_procedure pc on vt.visit_code = pc.visit_code
    where vt.index_start like @start_date
)
and vt.visit_code NOT IN (
SELECT
        distinct(v.visit_code)
    FROM
        hos_pt_visit v
    inner join hos_doctor_procedure pc on v.visit_code = pc.visit_code
WHERE
v.date_visit_sql BETWEEN @start_date AND @end_date
AND pc.icd9 = '8952'
)
and (TIMESTAMPDIFF(MINUTE,vt.index_start,vt.doctor_end)+10) <= 360
)