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

query รายเดือน
SET @start_date_ce='20250101';
SET @end_date_ce='20250131';
SET @start_date_be='25680101';
SET @end_date_be='25680131';
SET @start_quarter_ce='20250101';
SET @end_quarter_ce='20250331';
 
-- OPD Stats
SELECT 'OPD Stats' AS header;
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_ce AND @end_date_ce AND t.nhso IN ('WEL','UCS')
UNION ALL
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_ce AND @end_date_ce AND t.nhso='SSS'
UNION ALL
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_ce AND @end_date_ce AND t.nhso='OFC'
UNION ALL
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_ce AND @end_date_ce AND t.nhso='LGO'
UNION ALL
SELECT 'ทุกสิทธิ',COUNT(DISTINCT(v.visit_code)) FROM hos_pt_visit v WHERE v.date_visit_sql BETWEEN @start_date_ce AND @end_date_ce
UNION ALL
SELECT 'ไตรมาส',COUNT(DISTINCT(v.visit_code)) FROM hos_pt_visit v WHERE v.date_visit_sql BETWEEN @start_quarter_ce AND @end_quarter_ce;
 
-- IPD Stats
SELECT 'IPD Stats' AS header;
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_be AND @end_date_be AND t.nhso IN ('WEL','UCS')
UNION ALL
SELECT 'ประกันสังคม',COUNT(DISTINCT(v.an)) 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_be AND @end_date_be AND t.nhso='SSS'
UNION ALL
SELECT 'ข้าราชการ',COUNT(DISTINCT(v.an)) 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_be AND @end_date_be AND t.nhso='OFC'
UNION ALL
SELECT 'อปท.',COUNT(DISTINCT(v.an)) 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_be AND @end_date_be AND t.nhso='LGO'
UNION ALL
SELECT 'ทุกสิทธิ',COUNT(DISTINCT(v.an)) FROM ipd v WHERE v.datedsc BETWEEN @start_date_be AND @end_date_be;
 
-- LOS Stats
SELECT 'LOS Stats' AS header;
SELECT 'รวม LOS' AS type,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_be AND @end_date_be;
 
-- Chronic Stats
SELECT 'Chronic Disease Stats' AS header;
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_ce AND @end_quarter_ce AND d.icd10 BETWEEN 'E10' AND 'E14'
UNION ALL
SELECT 'ความดัน',COUNT(DISTINCT(v.hn)) 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_ce AND @end_quarter_ce AND d.icd10 BETWEEN 'I10' AND 'I15';
 
-- RW Stats
SELECT 'RW Stats' AS header;
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_be AND @end_date_be AND t.nhso IN ('WEL','UCS')
UNION ALL
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_be AND @end_date_be AND t.nhso='SSS'
UNION ALL
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_be AND @end_date_be AND t.nhso='OFC'
UNION ALL
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_be AND @end_date_be AND t.nhso='LGO'
UNION ALL
SELECT 'ทั้งหมด',SUM(rw.adjrw) FROM hos_ipd_rw rw INNER JOIN ipd v ON rw.an=v.an WHERE v.datedsc BETWEEN @start_date_be AND @end_date_be