visit ผู้ป่วยนอก(OPD) และผู้ป่วยใน (IPD)

SET @startdate = '2022-10-01';
SET @enddate = '2025-09-30';
 
SELECT
    (YEAR(calendar.cal_date) + IF(MONTH(calendar.cal_date) >= 10, 1, 0)) AS fiscal_year,
    DATE_FORMAT(calendar.cal_date, '%Y-%m') AS calendar_month_year,
 
    COALESCE(opd.opd_patients, 0) AS total_opd_patients,
    COALESCE(ipd.ipd_patients, 0) AS total_ipd_patients
FROM (
    SELECT DISTINCT DATE_FORMAT(vstdate, '%Y-%m-01') AS cal_date
    FROM ovst
    WHERE vstdate BETWEEN @startdate AND @enddate
    UNION DISTINCT
    SELECT DISTINCT DATE_FORMAT(regdate, '%Y-%m-01') AS cal_date
    FROM ipt
    WHERE regdate BETWEEN @startdate AND @enddate
) AS calendar
LEFT JOIN (
    SELECT
        DATE_FORMAT(vstdate, '%Y-%m-01') AS month_start_date,
        COUNT(DISTINCT hn) AS opd_patients
    FROM ovst
    WHERE vstdate BETWEEN @startdate AND @enddate
    GROUP BY month_start_date
) AS opd ON calendar.cal_date = opd.month_start_date
LEFT JOIN (
    SELECT
        DATE_FORMAT(regdate, '%Y-%m-01') AS month_start_date,
        COUNT(DISTINCT hn) AS ipd_patients
    FROM ipt
    WHERE regdate BETWEEN @startdate AND @enddate
    GROUP BY month_start_date
) AS ipd ON calendar.cal_date = ipd.month_start_date
ORDER BY
    calendar.cal_date ASC