พี่ภา รายเดือน ข้อมูลบริการ

SET @start_date = '20241201';
SET @end_date = '20241231';
 
SET @start_quarter = '20241001';
SET @end_quarter = '20241231';
 
(
    SELECT
        'UC' AS 'type',
        COUNT(DISTINCT(v.visit_code)) AS 'visit'
    FROM
        hos_pt_visit v
    INNER JOIN hos_code_pttype t ON v.pttype = t. CODE
    WHERE
        v.date_visit_sql BETWEEN @start_date
    AND @end_date
    AND t.nhso IN ('WEL', 'UCS')
)
UNION
    (
        SELECT
            'ประกันสังคม',
            COUNT(DISTINCT(v.visit_code))
        FROM
            hos_pt_visit v
        INNER JOIN hos_code_pttype t ON v.pttype = t. CODE
        WHERE
            v.date_visit_sql BETWEEN @start_date
        AND @end_date
        AND t.nhso = 'SSS'
    )
UNION
    (
        SELECT
            'ข้าราชการ',
            COUNT(DISTINCT(v.visit_code))
        FROM
            hos_pt_visit v
        INNER JOIN hos_code_pttype t ON v.pttype = t. CODE
        WHERE
            v.date_visit_sql BETWEEN @start_date
        AND @end_date
        AND t.nhso = 'OFC'
    )
UNION
    (
        SELECT
            'อปท.',
            COUNT(DISTINCT(v.visit_code))
        FROM
            hos_pt_visit v
        INNER JOIN hos_code_pttype t ON v.pttype = t. CODE
        WHERE
            v.date_visit_sql BETWEEN @start_date
        AND @end_date
        AND t.nhso = 'LGO'
    )
UNION
    (
        SELECT
            'ทุกสิทธิ',
            COUNT(DISTINCT(v.visit_code))
        FROM
            hos_pt_visit v
        WHERE
            v.date_visit_sql BETWEEN @start_date
        AND @end_date
    )
UNION
    (
        SELECT
            'ไตรมาส',
            COUNT(DISTINCT(v.visit_code))
        FROM
            hos_pt_visit v
        WHERE
            v.date_visit_sql BETWEEN @start_quarter
        AND @end_quarter
    )
 
 -- SET @start_date = '25671001';
    -- SET @end_date = '25671031';
    -- --
    -- (
    -- SELECT
    -- 'UC' AS 'type',
    -- COUNT(DISTINCT(v.an)) AS 'visit'
    -- FROM
    -- ipd v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- AND t.nhso IN ('WEL', 'UCS')
    -- )
    -- UNION
    -- (
    -- SELECT
    -- 'ประกันสังคม',
    -- COUNT(DISTINCT(v.an)) AS 'visit'
    -- FROM
    -- ipd v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- AND t.nhso = 'SSS'
    -- )
    -- UNION
    -- (
    -- SELECT
    -- 'ข้าราชการ',
    -- COUNT(DISTINCT(v.an)) AS 'visit'
    -- FROM
    -- ipd v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- AND t.nhso = 'OFC'
    -- )
    -- UNION
    -- (
    -- SELECT
    -- 'อปท.',
    -- COUNT(DISTINCT(v.an)) AS 'visit'
    -- FROM
    -- ipd v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- AND t.nhso = 'LGO'
    -- )
    -- UNION
    -- (
    -- SELECT
    -- 'ทุกสิทธิ',
    -- COUNT(DISTINCT(v.an)) AS 'visit'
    -- FROM
    -- ipd v
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- )
    --
    -- SET @start_date = '25671001';
    -- SET @end_date = '25671031';
    -- --
    -- SELECT
    -- sum(
    -- CASE
    -- WHEN rw.los IS NULL THEN
    -- 3
    -- ELSE
    -- rw.los
    -- END
    -- ) AS 'los'
    -- FROM
    -- ipd v
    -- LEFT JOIN hos_ipd_rw rw ON v.an = rw.an
    -- WHERE
    -- v.datedsc BETWEEN @start_date
    -- AND @end_date
    -- SET @start_quarter = '20241001';
    -- SET @end_quarter = '20241031';
    -- --
    -- (
    -- SELECT
    -- 'เบาหวาน' AS 'chronic',
    -- COUNT(DISTINCT(v.hn)) AS 'result'
    -- FROM
    -- hos_pt_visit v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_doctor_diagnosis d ON v.visit_code = d.visit_code
    -- WHERE
    -- v.date_visit_sql BETWEEN @start_quarter
    -- AND @end_quarter
    -- AND d.icd10 BETWEEN 'E10'
    -- AND 'E14'
    -- )
    -- UNION
    -- (
    -- SELECT
    -- 'ความดัน',
    -- COUNT(DISTINCT(v.hn)) AS 'result'
    -- FROM
    -- hos_pt_visit v
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    -- INNER JOIN hos_doctor_diagnosis d ON v.visit_code = d.visit_code
    -- WHERE
    -- v.date_visit_sql BETWEEN @start_quarter
    -- AND @end_quarter
    -- AND d.icd10 BETWEEN 'I10'
    -- AND 'I15'
    -- )
    -- SET @start_date = '25671001';
    --
    -- SET @end_date = '25671031';
    --
    -- (
    --
    -- SELECT
    --
    -- 'UC' AS 'type',
    --
    -- sum(rw.adjrw) AS 'Adj.RW'
    --
    -- FROM
    --
    -- hos_ipd_rw rw
    --
    -- INNER JOIN ipd v ON rw.an = v.an
    --
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    --
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    --
    -- WHERE
    --
    -- v.datedsc BETWEEN @start_date
    --
    -- AND @end_date
    --
    -- AND t.nhso IN ('WEL', 'UCS')
    --
    -- )
    --
    -- UNION
    --
    -- (
    --
    -- SELECT
    --
    -- 'ประกันสังคม',
    --
    -- sum(rw.adjrw)
    --
    -- FROM
    --
    -- hos_ipd_rw rw
    --
    -- INNER JOIN ipd v ON rw.an = v.an
    --
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    --
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    --
    -- WHERE
    --
    -- v.datedsc BETWEEN @start_date
    --
    -- AND @end_date
    --
    -- AND t.nhso = 'SSS'
    --
    -- )
    --
    -- UNION
    --
    -- (
    --
    -- SELECT
    --
    -- 'ข้าราชการ',
    --
    -- sum(rw.adjrw)
    --
    -- FROM
    --
    -- hos_ipd_rw rw
    --
    -- INNER JOIN ipd v ON rw.an = v.an
    --
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    --
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    --
    -- WHERE
    --
    -- v.datedsc BETWEEN @start_date
    --
    -- AND @end_date
    --
    -- AND t.nhso = 'OFC'
    --
    -- )
    --
    -- UNION
    --
    -- (
    --
    -- SELECT
    --
    -- 'อปท.',
    --
    -- sum(rw.adjrw)
    --
    -- FROM
    --
    -- hos_ipd_rw rw
    --
    -- INNER JOIN ipd v ON rw.an = v.an
    --
    -- INNER JOIN hos_pt p ON v.hn = p.hn
    --
    -- INNER JOIN hos_code_pttype t ON p.pttype = t. CODE
    --
    -- WHERE
    --
    -- v.datedsc BETWEEN @start_date
    --
    -- AND @end_date
    --
    -- AND t.nhso = 'LGO'
    --
    -- )
    --
    -- UNION
    --
    -- (
    --
    -- SELECT
    --
    -- 'ทั้งหมด',
    --
    -- sum(rw.adjrw)
    --
    -- FROM
    --
    -- hos_ipd_rw rw
    --
    -- INNER JOIN ipd v ON rw.an = v.an
    --
    -- WHERE
    --
    -- v.datedsc BETWEEN @start_date
    --
    -- AND @end_date
    --
    -- )