query ห้องตรวจกายภาพ จาก diag

-- 1. ตั้งค่าตัวแปรสำหรับรหัส ICD10 ที่ต้องการค้นหา
SET @ICD10_Code = 'M179';
 
-- 2. Query หลักโดยใช้ตัวแปร
SELECT
    '2565' AS fiscal_year,
    COUNT(DISTINCT o.vn) AS visit_count
FROM
    ovst AS o
INNER JOIN
    ovstdiag d ON o.vn = d.vn
WHERE
    o.main_dep = '005'
    AND o.vstdate BETWEEN '2021-10-01' AND '2022-09-30'
    AND d.icd10 = @ICD10_Code
 
UNION ALL
 
SELECT
    '2566' AS fiscal_year,
    COUNT(DISTINCT o.vn) AS visit_count
FROM
    ovst AS o
INNER JOIN
    ovstdiag d ON o.vn = d.vn
WHERE
    o.main_dep = '005'
    AND o.vstdate BETWEEN '2022-10-01' AND '2023-09-30'
    AND d.icd10 = @ICD10_Code
 
UNION ALL
 
SELECT
    '2567' AS fiscal_year,
    COUNT(DISTINCT o.vn) AS visit_count
FROM
    ovst AS o
INNER JOIN
    ovstdiag d ON o.vn = d.vn
WHERE
    o.main_dep = '005'
    AND o.vstdate BETWEEN '2023-10-01' AND '2024-09-30'
    AND d.icd10 = @ICD10_Code
 
UNION ALL
 
SELECT
    '2568' AS fiscal_year,
    COUNT(DISTINCT o.vn) AS visit_count
FROM
    ovst AS o
INNER JOIN
    ovstdiag d ON o.vn = d.vn
WHERE
    o.main_dep = '005'
    AND o.vstdate BETWEEN '2024-10-01' AND '2025-09-30'
    AND d.icd10 = @ICD10_Code