create or replace procedure SP_BA_SSXGBB( J_KSRQYM in char, -- 开始日期 J_JSRQYM in char -- 结束日期 ) as -- MODIFICATION HISTORY -- Person Date Comments -- shieq 2023.07.18 create 手术相关报表 BAGLTYB-20230511-003 -- czc 2023.08.04 调整三四级手术过滤条件 -- czc 2023.08.07 新增合计列，调整I 类切口手术人次数统计 -- shieq 2023.08.22 BAGLTYB-20230811-001 归档 -- shieq 2023.09.08 BAGLTYB-20230822-002 百分比添加% -- shieq 2023.09.11 BAGLTYB-20230822-001 添加 全部科室 行 -- czc 2023.09.12 调整择期手术人数统计，不足1周岁年龄限制条件调整,主诊断包含C00-C09条件 vSSLS0 number(11); --出院患者手术人数 vCYHZS number(11); --出院患者人次数 vWCSS0 number(11); --出院患者微创手术人数 vSIJSS number(11); --出院患者四级手术人数 vSAJSS number(11); --出院患者三级手术人数 vBFZLS number(11); --并发症发生例数 vZQLS0 number(11); --择期手术患者人数 vYBLS0 number(11); --I 类切口手术部位感染人次数 vYLLS0 number(11); --I 类切口手术人次数 vCYHZZB varchar2(18);--出院患者手术占比 vWCSSZB varchar2(18);--出院患者微创手术占比 vSIJSSZB varchar2(18);--四级手术占比 vSAJSSZB varchar2(18);--三级手术占比 vBFZZB varchar2(18);--手术患者并发症发生率 vYBLSZB varchar2(18);--I类切口手术部位感染率 cursor CUR_KS is select CYKBMC from BA_BRZYXX where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM group by CYKBMC; Row_KS CUR_KS%rowtype; begin delete from BA_YLZLZB; open CUR_KS; loop fetch CUR_KS into Row_KS; exit when CUR_KS%notfound; select count(1) into vCYHZS from BA_BRZYXX where CYRQ00>=J_KSRQYM and CYKBMC= Row_KS.CYKBMC and CYRQ00<=J_JSRQYM ; select count(distinct BA.LSH000) into vSSLS0 from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and (XT.LB0000='手术' or XT.LB0000='介入治疗') ; --微创 select count(distinct BA.LSH000) into vWCSS0 from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and XT.SFWC00='1' and (XT.LB0000='手术' or XT.LB0000='介入治疗') ; --四级 select count(distinct BA.LSH000) into vSIJSS from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and XT.SSDJBH='0' and (XT.LB0000='手术' or XT.LB0000='介入治疗') ; --三级 select count(distinct BA.LSH000) into vSAJSS from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 join XT_CM3000 xt on XT.CM3000 =SS.SSM000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and XT.SSDJBH='1' and (XT.LB0000='手术' or XT.LB0000='介入治疗') ; --并发症发生例数 select count(distinct BA.LSH000) into vBFZLS from BA_BRZYXX BA join bq_baszdb zd on BA.LSH000 =zd.zyid00 join xt_icd900 xt on XT.Icd900 =zd.zdm000 left join XT_ICD9FB fb on fb.Icd900 =zd.zdm000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and fb.SFSSBF='1' and zd.zdlb00 in ('3','4') and zd.rybq00 ='无'; --择期手术患者人数 select count(distinct BA.LSH000) into vZQLS0 from BA_BRZYXX BA join bq_baszdb zd on BA.LSH000 =zd.zyid00 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and zd.zdlb00 in ('3','4') and ba.rytj00 <>'1' and substr(zd.zdm000,0,3) not between 'O00' and 'O99' and (nvl(ba.brnlmm,0)*30 + nvl(ba.brnldd,0)) > 28 and BA.LSH000 not in (select BA.LSH000 from BA_BRZYXX BA join bq_baszdb zd on BA.LSH000 =zd.zyid00 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and zd.zdlb00 ='3' and substr(zd.zdm000,0,3) between 'C00' and 'C97' ); --I 类切口手术部位感染人次数 select count(distinct BA.LSH000) into vYBLS0 from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and SS.Qkyhdj in ('Ⅰ/丙','Ⅰ丙') ; --I 类切口手术人次数 select count(distinct BA.LSH000) into vYLLS0 from BA_BRZYXX BA join BA_BASSSB SS on BA.LSH000=SS.LSH000 where CYRQ00>=J_KSRQYM and CYRQ00<=J_JSRQYM and CYKBMC= Row_KS.CYKBMC and instrb(QKYHDJ,'Ⅰ')>0 ; select case when vCYHZS=0 then '0.00%' else to_char(round(vSSLS0*100/vCYHZS,2),'fm9999990.00')||'%' end , case when vSSLS0=0 then '0.00%' else to_char(round(vSIJSS*100/vSSLS0,2),'fm9999990.00')||'%' end , case when vSSLS0=0 then '0.00%' else to_char(round(vSAJSS*100/vSSLS0,2),'fm9999990.00')||'%' end , case when vSSLS0=0 then '0.00%' else to_char(round(vWCSS0*100/vSSLS0,2),'fm9999990.00')||'%' end , case when vSSLS0=0 then '0.00%' else to_char(round(vBFZLS*100/vSSLS0,2),'fm9999990.00')||'%' end , case when vYLLS0=0 then '0.00%' else to_char(round(vYBLS0*100/vYLLS0,2),'fm9999990.00')||'%' end into vCYHZZB,vSIJSSZB,vSAJSSZB,vWCSSZB,vBFZZB,vYBLSZB from dual; insert into BA_YLZLZB(F1,F2,F3,F4,F5,F6, F7,F8,F9,F10,F11,F12, F13,F14,F15,F16) values(Row_KS.CYKBMC,vCYHZS,vSSLS0,vCYHZZB,vWCSS0,vWCSSZB,vSIJSS,vSIJSSZB ,vSAJSS,vSAJSSZB,vBFZLS,vZQLS0,vBFZZB,vYBLS0,vYLLS0,vYBLSZB); end loop; close CUR_KS; commit; insert into BA_YLZLZB(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16) select '全部科室', sum(F2),--出院患者人次数 sum(F3),case when sum(F2)=0 then '0.00%' else to_char(round(sum(F3)*100/sum(F2),2),'Fm9999990.00')||'%' end ,--出院患者手术人次数及占比 sum(F5),case when sum(F3)=0 then '0.00%' else to_char(round(sum(F5)*100/sum(F3),2),'Fm9999990.00')||'%' end ,--出院患者微创手术人次数及占比 sum(F7),case when sum(F3)=0 then '0.00%' else to_char(round(sum(F7)*100/sum(F3),2),'Fm9999990.00')||'%' end ,--出院患者四级手术人次数及占比 sum(F9),case when sum(F3)=0 then '0.00%' else to_char(round(sum(F9)*100/sum(F3),2),'Fm9999990.00')||'%' end ,--出院患者三级手术人次数及占比 sum(F11),--出院患者并发症人次数 sum(F12),--择期患者手术人数 --sum(F13),--出院患者I类切口手术部位感染人次数 case when sum(F3)=0 then '0.00%' else to_char(round(sum(F11)*100/sum(F3),2),'Fm9999990.00')||'%'end , sum(F14),--出院患者I类切口手术人次数 sum(F15),case when sum(F15)=0 then '0.00%' else to_char(round(sum(F14)*100/sum(F15),2),'Fm9999990.00')||'%' end --出院患者I类切口手术人次数占比 from BA_YLZLZB; commit; end; /