ค่ารักษา OPD และ IPD

SET @icd10 = 'J101';
SET @start_date = '2021-10-01';
SET @end_date = '2022-09-30';
 
SELECT
    'OPD' AS service_type,
    FORMAT(SUM(opitemrece.sum_price), 2) AS total_cost
FROM opitemrece
WHERE opitemrece.vn IN (
    -- OPD cases with I10 diagnosis within date range
    SELECT ovst.vn
    FROM ovst
    INNER JOIN ovstdiag ON ovst.vn = ovstdiag.vn
    WHERE ovstdiag.icd10 = @icd10
        AND ovstdiag.diagtype = '1'
    AND ovst.vstdate BETWEEN @start_date AND @end_date
)
 
UNION ALL
 
SELECT
    'IPD' AS service_type,
    FORMAT(SUM(opitemrece.sum_price), 2) AS total_cost
FROM opitemrece
WHERE opitemrece.an IN (
    -- IPD cases with I10 diagnosis within date range
    SELECT ipt.an
    FROM ipt
    INNER JOIN iptdiag ON ipt.an = iptdiag.an
    WHERE iptdiag.icd10 = @icd10
        AND iptdiag.diagtype = '1'
    AND ipt.regdate BETWEEN @start_date AND @end_date
);

SET @icd10 = 'J101';
SET @start_date = '2024-10-01';
SET @end_date = '2025-09-30';
 
SELECT
    'OPD' AS service_type,
    FORMAT(SUM(opitemrece.sum_price), 2) AS total_cost
FROM opitemrece
WHERE opitemrece.vn IN (
    -- OPD cases with I10 diagnosis within date range
    SELECT ovst.vn
    FROM ovst
    INNER JOIN ovstdiag ON ovst.vn = ovstdiag.vn
        INNER JOIN patient p ON p.hn = opitemrece.hn
    WHERE ovstdiag.icd10 = @icd10
        AND ovstdiag.diagtype = '1'
    AND ovst.vstdate BETWEEN @start_date AND @end_date
        AND (DATEDIFF(opitemrece.vstdate, p.birthday) / 365.25) BETWEEN 0.5 AND 15
)
 
UNION ALL
 
SELECT
    'IPD' AS service_type,
    FORMAT(SUM(opitemrece.sum_price), 2) AS total_cost
FROM opitemrece
inner JOIN patient p ON opitemrece.hn = p.hn
WHERE opitemrece.an IN (
    -- IPD cases with I10 diagnosis within date range
    SELECT ipt.an
    FROM ipt
    INNER JOIN iptdiag ON ipt.an = iptdiag.an
    WHERE iptdiag.icd10 = @icd10
        AND iptdiag.diagtype = '1'
    AND ipt.regdate BETWEEN @start_date AND @end_date
        AND TIMESTAMPDIFF(MONTH, p.birthday, opitemrece.vstdate) >= 6
    AND FLOOR(DATEDIFF(opitemrece.vstdate, p.birthday) / 365.25) <= 15
);