12
« เมื่อ: มิถุนายน 01, 2012, 11:22:35 AM »
ขอบคุณครับพี่ทัศน์
ผมเองก็ทำขึ้นมาเหมือนกันครับ แต่ใช้การดึงค่าใช้จ่ายทั้ง 17 หมวดหมู่จากจากตาราง opitemrece แยกตามหมวดหมู่ของ income และ income_group ครับ แต่จะมีปัญหาก็คือเรื่องของยานอกบัญชีและในบัญชี เพราะผมเช็คไม่ได้ว่าฟิลด์ ed กับ ned ดูจากไหน เลยให้เภสัชเขาเอาชื่อยานอกบัญชีทั้งหมดมาแล้วผมก็จัดการนับตาม icode แทนครับ ตามตัวอย่างครับ
OPD
==============
set @hosp = '11053';
set @start_date = '2010-10-01';
set @end_date = '2011-09-30';
select @hosp as hcode,
v.vn, v.hn, pt.cid, pt.pname, pt.fname, pt.lname,
pt.birthday,
pt.sex,
v.vstdate,
pttype.name as pttypename,
v.pdx, v.dx0, v.dx1, v.dx2, v.dx3, v.dx4, v.dx5,
v.op0, v.op1, v.op2, v.op3, v.op4, v.op5,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('01', '02','32','33','34','35','44') and vn=v.vn) as inc01,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('30', '18', '24') and vn=v.vn) as inc02,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '03' and icode not in ('1000035', '1000054', '1000205', '1500603', '1480505', '1460004','1460010','1000053','1000039', '1000134', '1480504', '1000262', '1511001', '1430506', '1450001', '1450002', '1450505', '1481004', '1000013', '1000014', '1490508', '1000010', '1490504', '1000051', '1450020','1450001', '1450002', '1440505', '1440101') and vn=v.vn and item_type <> 'H') as inc03,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '03' and vn=v.vn and item_type='H') as inc04,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '19' and vn=v.vn) as inc05,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '26' and vn=v.vn) as inc06,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('05', '27') and vn=v.vn) as inc07,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('04', '06') and vn=v.vn) as inc08,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('07', '23', '48', '50') and vn=v.vn) as inc09,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '24' and vn=v.vn) as inc10,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('08', '10', '11') and vn=v.vn) as inc11,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('13', '14', '15', '17','20','21','22','36','37','45','51','53','54','55') and vn=v.vn) as inc12,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '16' and vn=v.vn) as inc13,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '09' and vn=v.vn) as inc14,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '21' and vn=v.vn) as inc15,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '00' and vn=v.vn) as inc16,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where icode in ('1000035', '1000054', '1000205', '1500603', '1480505', '1460004','1460010','1000053','1000039', '1000134', '1480504', '1000262', '1511001', '1430506', '1450001', '1450002', '1450505', '1481004', '1000013', '1000014', '1490508', '1000010', '1490504', '1000051', '1450020','1450001', '1450002', '1440505', '1440101') and vn=v.vn) as inc17
from vn_stat v
left join patient pt on pt.hn=v.hn
left join pttype on pttype.pttype=v.pttype
where v.vstdate between @start_date and @end_date
--- limit 10
IPD
=================
set @hosp = '11053';
set @start_date = '2010-10-01';
set @end_date = '2011-09-30';
select @hosp as hcode,
ipt.hn, ipt.an, ipt.vn, pt.pname, pt.fname, pt.lname,
pt.birthday,
pt.sex,
ipt.regdate,
ward.name as wardname, pttype.name as pttypename,
ipt.dchdate,
an_stat.pdx, an_stat.dx0, an_stat.dx1, an_stat.dx2, an_stat.dx3, an_stat.dx4, an_stat.dx5,
an_stat.op0, an_stat.op1, an_stat.op2, an_stat.op3, an_stat.op4, an_stat.op5, an_stat.op6,
(select sum(sum_price) from opitemrece where income in ('01', '02','32','33','34','35','44') and an=ipt.an) as inc01,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('30', '18', '24') and an=ipt.an) as inc02,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '03' and icode not in ('1000035', '1000054', '1000205', '1500603', '1480505', '1460004','1460010','1000053','1000039', '1000134', '1480504', '1000262', '1511001', '1430506', '1450001', '1450002', '1450505', '1481004', '1000013', '1000014', '1490508', '1000010', '1490504', '1000051', '1450020','1450001', '1450002', '1440505', '1440101') and an=ipt.an and item_type <> 'H') as inc03,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '03' and an=ipt.an and item_type='H') as inc04,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '19' and an=ipt.an) as inc05,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '26' and an=ipt.an) as inc06,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('05', '27') and an=ipt.an) as inc07,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('04', '06') and an=ipt.an) as inc08,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('07', '23', '48', '50') and an=ipt.an) as inc09,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '24' and an=ipt.an) as inc10,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('08', '10', '11') and an=ipt.an) as inc11,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income in ('13', '14', '15', '17','20','21','22','36','37','45','51','53','54','55') and an=ipt.an) as inc12,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '16' and an=ipt.an) as inc13,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '09' and an=ipt.an) as inc14,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '21' and an=ipt.an) as inc15,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '00' and an=ipt.an) as inc16,
(select IFNULL(sum(sum_price), '0.00') from opitemrece where icode in ('1000035', '1000054', '1000205', '1500603', '1480505', '1460004','1460010','1000053','1000039', '1000134', '1480504', '1000262', '1511001', '1430506', '1450001', '1450002', '1450505', '1481004', '1000013', '1000014', '1490508', '1000010', '1490504', '1000051', '1450020','1450001', '1450002', '1440505', '1440101') and an=ipt.an) as inc17,
-- (select IFNULL(sum(sum_price), '0.00') from opitemrece where income = '03' and an=ipt.an) as drug,
an_stat.admdate as los, ipt.drg, ipt.rw, ipt.adjrw
from ipt
left join patient pt on pt.hn=ipt.hn
left join ward on ward.ward=ipt.ward
left join an_stat on an_stat.an=ipt.an
left join pttype on pttype.pttype=an_stat.pttype
where ipt.dchdate between @start_date and @end_date
-- limit 10
ผมก็ยังสงสัยว่า los มันคือค่าอะไรเพราะใน an_stat ผมมันว่าง ผมเลยเดาเอาว่าน่าจะเป็นวันนอน เลยดึงเอา admdate แทน ครับ