นับจำนวนวันนอน

  1. Overview
  2. HosXP
  3. Query
  4. นับจำนวนวันนอน

รายคน

SET @startdate = '2022-10-01';
SET @enddate = '2025-09-30';
SELECT
    a.an,
    a.regdate,
    a.dchdate,
    CONCAT(p.pname, p.fname, ' ', p.lname) AS pt_name,
    a.admdate,
    w.name AS w_name,
    (SELECT bedno FROM ipt_bed_stat WHERE an = a.an ORDER BY ipt_bed_stat_id DESC LIMIT 1) AS bedno
FROM
    an_stat a
LEFT JOIN
    patient p ON p.hn = a.hn
LEFT JOIN
    ipt i ON i.an = a.an
LEFT JOIN
    ward w ON w.ward = i.ward
WHERE
    a.regdate BETWEEN @startdate AND @enddate

รายเดือน แยก ward

SELECT
    i.ward AS wardcode,
    w.name AS wardname,
    -- FY 2566 (Oct 2022 - Sep 2023)
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 1 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ต.ค. 2022`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 2 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ย. 2022`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 3 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ธ.ค. 2022`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 4 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ม.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 5 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.พ. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 6 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มี.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 7 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `เม.ย. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 8 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 9 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มิ.ย. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 10 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 11 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ส.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 12 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2023) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ย. 2023`,
 
    -- FY 2024 (Oct 2023 - Sep 2024)
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 1 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ต.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 2 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ย. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 3 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ธ.ค. 2023`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 4 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ม.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 5 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.พ. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 6 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มี.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 7 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `เม.ย. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 8 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 9 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มิ.ย. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 10 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 11 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ส.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 12 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2024) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ย. 2024`,
 
    -- FY 2568 (Oct 2024 - Sep 2025)
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 1 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ต.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 2 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ย. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 3 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ธ.ค. 2024`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 4 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ม.ค. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 5 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.พ. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 6 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มี.ค. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 7 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `เม.ย. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 8 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `พ.ค. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 9 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `มิ.ย. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 10 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ค. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 11 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ส.ค. 2025`,
    SUM(CASE WHEN (MOD(MONTH(i.regdate) - 10 + 12, 12) + 1 = 12 AND (YEAR(i.regdate) + IF(MONTH(i.regdate) >= 10, 1, 0)) = 2025) THEN DATEDIFF(an_stat.dchdate, i.regdate) ELSE 0 END) AS `ก.ย. 2025`,
 
    SUM(DATEDIFF(an_stat.dchdate, i.regdate)) AS รวมวันนอน
FROM
    ipt i
INNER JOIN
    an_stat an_stat ON i.an = an_stat.an
LEFT JOIN
    ward w ON i.ward = w.ward
WHERE
    i.regdate BETWEEN '2022-10-01' AND '2025-09-30'
    AND an_stat.dchdate IS NOT NULL
GROUP BY
    i.ward, w.name, w.bedcount
ORDER BY
    i.ward ASC;