151
Report Exchange / Re: รายงาน CMI และ MDC
« เมื่อ: ตุลาคม 31, 2012, 13:32:32 PM »
หรือจะลองแบบนี้
select i.mdc
,count(i.an) 'Total_case'
,sum(i.adjrw) 'Total_AdjRW'
,avg(i.adjrw) 'Total_CMI'
,sum(substring(i.drg,3,2) between '01' and '49') 'Total_OP_case'
,sum(if(substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'Total_OP_AdjRW'
,avg(if(substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'Total_OP_CMI'
,sum(substring(i.drg,3,2)>'49') 'Total_NonOP_case'
,sum(if(substring(i.drg,3,2)>'49',i.adjrw,0)) 'Total_NonOP_AdjRW'
,avg(if(substring(i.drg,3,2)>'49',i.adjrw,null)) 'Total_NonOP_CMI'
,sum(p.pttype_spp_id in (3,4)) 'UC_case'
,sum(if(p.pttype_spp_id in (3,4),i.adjrw,0)) 'UC_AdjRW'
,avg(if(p.pttype_spp_id in (3,4),i.adjrw,null)) 'UC_CMI'
,sum(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49') 'UC_OP_case'
,sum(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'UC_OP_AdjRW'
,avg(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'UC_OP_CMI'
,sum(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49') 'UC_NonOP_case'
,sum(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'UC_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'UC_NonOP_CMI'
,sum(p.pttype_spp_id in (1)) 'A2_case'
,sum(if(p.pttype_spp_id in (1),i.adjrw,0)) 'A2_AdjRW'
,avg(if(p.pttype_spp_id in (1),i.adjrw,null)) 'A2_CMI'
,sum(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49') 'A2_OP_case'
,sum(if(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'A2_OP_AdjRW'
,avg(if(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'A2_OP_CMI'
,sum(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49') 'A2_NonOP_case'
,sum(if(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'A2_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'A2_NonOP_CMI'
,sum(p.pttype_spp_id in (2)) 'A7_case'
,sum(if(p.pttype_spp_id in (2),i.adjrw,0)) 'A7_AdjRW'
,avg(if(p.pttype_spp_id in (2),i.adjrw,null)) 'A7_CMI'
,sum(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49') 'A7_OP_case'
,sum(if(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'A7_OP_AdjRW'
,avg(if(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'A7_OP_CMI'
,sum(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49') 'A7_NonOP_case'
,sum(if(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'A7_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'A7_NonOP_CMI'
,sum(p.pttype_spp_id in (5,6)) 'Other_case'
,sum(if(p.pttype_spp_id in (5,6),i.adjrw,0)) 'Other_AdjRW'
,avg(if(p.pttype_spp_id in (5,6),i.adjrw,null)) 'Other_CMI'
,sum(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49') 'Other_OP_case'
,sum(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'Other_OP_AdjRW'
,avg(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'Other_OP_CMI'
,sum(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49') 'Other_NonOP_case'
,sum(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'Other_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'Other_NonOP_CMI'
from ipt i
left join pttype p on p.pttype=i.pttype
where i.dchdate between '2011-10-1' and '2012-9-30'
group by i.mdc
ลองหาไฟล์ MDCName มาประกอบดูคับ
select i.mdc
,count(i.an) 'Total_case'
,sum(i.adjrw) 'Total_AdjRW'
,avg(i.adjrw) 'Total_CMI'
,sum(substring(i.drg,3,2) between '01' and '49') 'Total_OP_case'
,sum(if(substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'Total_OP_AdjRW'
,avg(if(substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'Total_OP_CMI'
,sum(substring(i.drg,3,2)>'49') 'Total_NonOP_case'
,sum(if(substring(i.drg,3,2)>'49',i.adjrw,0)) 'Total_NonOP_AdjRW'
,avg(if(substring(i.drg,3,2)>'49',i.adjrw,null)) 'Total_NonOP_CMI'
,sum(p.pttype_spp_id in (3,4)) 'UC_case'
,sum(if(p.pttype_spp_id in (3,4),i.adjrw,0)) 'UC_AdjRW'
,avg(if(p.pttype_spp_id in (3,4),i.adjrw,null)) 'UC_CMI'
,sum(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49') 'UC_OP_case'
,sum(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'UC_OP_AdjRW'
,avg(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'UC_OP_CMI'
,sum(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49') 'UC_NonOP_case'
,sum(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'UC_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (3,4) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'UC_NonOP_CMI'
,sum(p.pttype_spp_id in (1)) 'A2_case'
,sum(if(p.pttype_spp_id in (1),i.adjrw,0)) 'A2_AdjRW'
,avg(if(p.pttype_spp_id in (1),i.adjrw,null)) 'A2_CMI'
,sum(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49') 'A2_OP_case'
,sum(if(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'A2_OP_AdjRW'
,avg(if(p.pttype_spp_id in (1) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'A2_OP_CMI'
,sum(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49') 'A2_NonOP_case'
,sum(if(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'A2_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (1) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'A2_NonOP_CMI'
,sum(p.pttype_spp_id in (2)) 'A7_case'
,sum(if(p.pttype_spp_id in (2),i.adjrw,0)) 'A7_AdjRW'
,avg(if(p.pttype_spp_id in (2),i.adjrw,null)) 'A7_CMI'
,sum(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49') 'A7_OP_case'
,sum(if(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'A7_OP_AdjRW'
,avg(if(p.pttype_spp_id in (2) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'A7_OP_CMI'
,sum(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49') 'A7_NonOP_case'
,sum(if(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'A7_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (2) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'A7_NonOP_CMI'
,sum(p.pttype_spp_id in (5,6)) 'Other_case'
,sum(if(p.pttype_spp_id in (5,6),i.adjrw,0)) 'Other_AdjRW'
,avg(if(p.pttype_spp_id in (5,6),i.adjrw,null)) 'Other_CMI'
,sum(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49') 'Other_OP_case'
,sum(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49',i.adjrw,0)) 'Other_OP_AdjRW'
,avg(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2) between '01' and '49',i.adjrw,null)) 'Other_OP_CMI'
,sum(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49') 'Other_NonOP_case'
,sum(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49',i.adjrw,0)) 'Other_NonOP_AdjRW'
,avg(if(p.pttype_spp_id in (5,6) and substring(i.drg,3,2)>'49',i.adjrw,null)) 'Other_NonOP_CMI'
from ipt i
left join pttype p on p.pttype=i.pttype
where i.dchdate between '2011-10-1' and '2012-9-30'
group by i.mdc
ลองหาไฟล์ MDCName มาประกอบดูคับ