友恒通有限公司
3) 现金支付1与现金支付的区别
4）平衡关系：总金额+舍入=当年账户支付+历年账户支付+统筹支付+附加支付+现金支付+支票支付+其他记帐+欠款+充值卡支付+优惠
5）为何分步求和？
13）报表栏目的合并
方法一：通过关键字段关联连接，适合一个表的记录完全包含另一表的记录 select a.keyfield,a.field1,b.field2 from #temp1 a,#temp2 b where a.keyfield*=b.keyfield
方法二：生成一个全部字段表，再用另一表中的字段替换，适合一个表的记录完全包含另一表的记录。
方法三：先建一临时表（全部字段），分别插入对应字段，再求和。
三、常用报表的取数方法
1）按执行科室统计门诊收费总金额等
select a.yfdm,sum(b.ypsl*b.ts*b.cfts*b.ylsj/b.ykxs) zje
from VW_MZCFK a (nolock), VW_MZCFMXK b (nolock) ,VW_MZBRJSK c (nolock) where c.sfrq between @ksrq and @jsrq+’24’ and c.ybjszt=2 and c.jlzt in (0,1,2)
and ghsfbz=1 --收费
and c.sjh=a.jssjh and a.xh=b.cfxh group by a.yfm
2）按医生科室统计门诊收费总金额、发票数等 select ksdm,convert(numeric(14,2),sum(zje)) zje,
sum(case when a.jlzt in (0,1) then 1 else 0 end) fpzs --不按处方打发票 From VW_MZBRJSK (nolock)
Where sfrq between @ksrq and @jsrq+ '24' and ybjszt=2 and jlzt in (0,1,2) and ghsfbz=1 --收费 Group by ksdm
3）按医生科室统计门诊收费总金额，药费 select a.ksdm ,
convert(numeric(14,2),sum(case when c.ypbz in (1,2,3) then b.xmje else 0 end)) ypje, convert(numeric(14,2),sum(b.xmje)) zje
From VW_MZBRJSK a (nolock),VW_MZBRJSMXK b (nolock),YY_SFDXMK c Where sfrq between @ksrq and @jsrq+ '24' and a.ybjszt=2 and a.jlzt in (0,1,2)
and ghsfbz=1 --收费 and a.sjh=b.jssjh
第 26 页 共 53 页
友恒通有限公司
and b.dxmdm=c.id Group by a.ksdm
4）按收费大项目统计总金额
select b.dxmdm, convert(numeric(14,2),sum(b.xmje)) zje,
convert(numeric(14,2),sum(b.zfje)) zfje
From VW_MZBRJSK a (nolock),VW_MZBRJSMXK b (nolock)
Where a.sfrq between @ksrq and @jsrq+ '24' and a.ybjszt=2 and a.jlzt in (0,1,2) and ghsfbz=1 --收费 and a.sjh=b.jssjh Group by b.dxmdm
5）按费用发生时病人所在科室统计住院结帐病人费用
select b.ksdm,sum(case when a.jlzt in (0,2) then b.zje else –b.zje end) zje,
sum(case when c.ypbz in (1,2,3) and a.jlzt in (0,2) then b.zje
when c.ypbz in (1,2,3) and a.jlzt in (1) then -b.zje else 0 end) ypje,
count(distinct a.syxh) jzrs
from ZY_BRJSK a (nolock),ZY_BRFYMXK b (nolock),YY_SFDXMK c (nolock)
where a.jsrq between @ksrq and @jsrq+'24' and a.ybjszt=2 and a.jlzt in (0,1,2) and a.syxh=b.syxh and b.zxrq between a.ksrq and a.jzrq and b.dxmdm=c.id group by b.ksdm
--据此计算次均费用, 日均费用，均住院天，人均药费，药占比例
6）按病人当前所在科室统计住院结帐病人费用
select b.ksdm,sum(a.zje) zje，count(distinct a.syxh) jzrs，sum(a.zyts) zyts from ZY_BRJSK a (nolock),ZY_BRSYK b (nolock)
where a.jsrq between @ksrq and @jsrq+'24' and a.ybjszt=2 and a.jlzt in (0,1,2) and a.syxh=b.syxh group by b.ksdm
7）出院人数和住院天数
select count(distinct a.syxh) jzrs，sum(a.zyts) zyts from ZY_BRJSK a (nolock)
where a.jsrq between @ksrq and @jsrq+'24' and a.ybjszt=2 and a.jlzt in (0,1,2) and jszt=2 --出院结算
8）住院发票补打
select a.blh \病历号\卡号\病人姓名\性别\
fph \发票号\医保类型\单位\
第 27 页 共 53 页
友恒通有限公司
substring(a.ryrq,1,4)+'.'+substring(a.ryrq,5,2)+'.'+substring(a.ryrq,7,2)
+' ' +substring(a.ryrq,9,8)+(case when substring(a.ryrq,9,2)<'12' then 'AM' else 'PM' end) \入院日期\
substring(a.cqrq,1,4)+'.'+substring(a.cqrq,5,2)+'.'+substring(a.cqrq,7,2)
+' ' +substring(a.cqrq,9,8)+(case when substring(a.cqrq,9,2)>='12' then 'PM' else 'AM' end) \出院日期\
c.zyts \住院天数\病区名称\科室\床位\ c.zje \总费用\记帐金额\
c.zfje-c.srje \自己负担\自费部分\欠费\ c.zje-c.zfyje \可报部分\
substring(c.jsrq,1,4)+'.'+substring(c.jsrq,5,2)+'.'+substring(c.jsrq,7,2)
+' ' +substring(c.jsrq,9,8)+ (case when substring(c.jsrq,9,2)<'12' then 'AM' else 'PM' end) \结算日期\
c.pzh \凭证\地址\联系人\电话\ c.dnzhye \当年账户余额\c.lnzhye \历年账户余额\
sum(case when isnull(lx,'')='01' then je else 0 end) \起付段当年账户支付\ sum(case when isnull(lx,'')='02' then je else 0 end) \起付段历年帐户支付\ sum(case when isnull(lx,'')='03' then je else 0 end) \起付段现金支付\
sum(case when isnull(lx,'')='04' then je else 0 end) \统筹段历年帐户支付\ sum(case when isnull(lx,'')='05' then je else 0 end) \统筹段现金支付\ sum(case when isnull(lx,'')='06' then je else 0 end) \统筹段统筹支付\
sum(case when isnull(lx,'')='07' then je else 0 end) \附加段历年帐户支付\ sum(case when isnull(lx,'')='08' then je else 0 end) \附加段现金支付\ sum(case when isnull(lx,'')='09' then je else 0 end) \附加段地方附加支付\from ZY_BRSYK a (nolock),ZY_BRXXK b (nolock),ZY_BRJSK c (nolock),
ZY_BQDMK d (nolock),YY_YBFLK e (nolock), ZY_BRJSJEK f (nolock),YY_KSBMK g (nolock)
where c.fph=@fph and a.syxh=c.syxh and a.patid=b.patid and c.jlzt=0
and c.jszt in (1,2) and a.bqdm=d.id and a.ybdm=e.ybdm and c.xh*=f.jsxh and a.ksdm*=g.id
group by a.blh,a.cardno,a.hzxm,a.sex,b.dwmc,a.ryrq, a.cqrq,a.cwdm,c.zyts,
d.name ,g.name,a.lxr,e.ybsm,e.rqflmc,c.zje ,c.zje-c.zfje+c.srje , c.zfje-c.srje ,c.zfyje,c.qfje, c.zje-c.zfyje , c.jsrq, c.pzh,b.lxdz, b.lxdh,fph,c.dnzhye,c.lnzhye
9）全院药品消耗统计 --药库
select a.cd_idm,sum(a.cksl) ypsl, sum(a.ckje_ls) lsje, sum(a.ckje_pf) pfje from YK_YPTZMXK a (nolock),YK_YPCDMLK b (nolock)
where a.cd_idm=b.idm and a.czrq between @ksrq and @jsrq+'24'
and charindex(b.yplh,@yplh)>0
and a.czdm in ('02','04','08','16','21')-- 报损,盘亏,退货,调亏,科室发药 group by a.cd_idm
第 28 页 共 53 页
友恒通有限公司
union all --药房
select a.cd_idm,sum(a.cksl) ypsl, sum(a.ckje_ls) lsje, sum(a.ckje_pf) pfje from YF_YPTZMXK a (nolock),YK_YPCDMLK b (nolock)
where a.cd_idm=b.idm and a.czrq between @ksrq and @jsrq+'24'
and charindex(b.yplh,@yplh)>0
and a.czdm in ('02','04','08','09','11','13','16','21') --报损,盘亏,退货,门诊发药,住院发药,出院带药,调亏,科室发药 group by a.cd_idm
10）药库药品出库统计
select isnull(c.cjmc,'无') \厂家名称\药品代码\药品名称\
b.ypgg \规格\零售价\数量\
c.ykdw \单位\零售金额\进销差额\from YK_YPCKZD a (nolock), YK_YPCKMX b (nolock), YK_YPCDMLK c (nolock)
where a.yfrq between @ksrq and @jsrq+'24' and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0 and a.jlzt=0 and a.qrbz=1 group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ykdw union
select isnull(c.cjmc,'无') \厂家名称\药品代码\药品名称\
b.ypgg \规格\零售价\数量\
c.ykdw \单位\零售金额\进销差额\from YK_KSFYZD a (nolock), YK_KSFYMX b (nolock), YK_YPCDMLK c (nolock)
where a.jzrq between @ksrq and @jsrq+'24' and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0 and a.jlzt=0
group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ylsj,c.ykdw union
select isnull(c.cjmc,'无') \厂家名称\药品代码\药品名称\
b.ypgg \规格\零售价\数量\
c.ykdw \单位\零售金额\进销差额\from YF_YFTKZD a (nolock), YF_YFTKMX b (nolock), YK_YPCDMLK c (nolock)
where a.ykrq between @ksrq and @jsrq+'24' and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0 and a.jlzt=0 and a.qrbz=1 group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ykdw
11．药库药品进货统计
select c.cjmc ,b.ypdm ,b.ypmc ,b.ypgg ,c.ykdw , max(c.ylsj) ylsj,sum(b.rksl/b.ykxs) ypsl,
sum(b.rksl*b.ylsj/b.ykxs) sum_lsje,
sum(b.jjje) sum_jjje,sum(b.jxce) sum_jxce
from YK_YPRKZD a(nolock),YK_YPRKMX b(nolock),YK_YPCDMLK c(nolock) where a.rkrq between @ksrq and @jsrq+'24'
第 29 页 共 53 页
友恒通有限公司
and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0 and a.gzbz<>1 and a.rkdm <> '02'
group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ykdw -- 正常入库和已冲正的 union
select c.cjmc ,b.ypdm ,b.ypmc ,b.ypgg,c.ykdw , max(c.ylsj) ylsj,sum(b.rksl/b.ykxs) ypsl ,
sum(b.rksl*b.ylsj/b.ykxs) sum_lsje,
sum(b.jjje) sum_jjje,sum(b.jxce) sum_jxce
from YK_YPRKZD a(nolock),YK_YPRKMX b(nolock),YK_YPCDMLK c(nolock) where a.rkrq between @ksrq and @jsrq+'24'
and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0
and ((a.rkdm = '02' and a.dpbz=0) or (a.rkdm='00' and a.dpbz=1)) group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ykdw -- 挂帐入库不包括已冲证 union
select c.cjmc ,b.ypdm ,b.ypmc ,b.ypgg ,c.ykdw ,
max(c.ylsj),-sum(b.thsl/b.ykxs) ,-sum(b.thsl*b.ylsj/b.ykxs) sum_lsje, -sum(b.thje) sum_jjje,sum(b.jxce) sum_jxce
from YK_YPTHZD a(nolock),YK_YPTHMX b(nolock),YK_YPCDMLK c(nolock) where a.jzrq between @ksrq and @jsrq
and a.xh=b.zd_xh and b.cd_idm=c.idm and a.jzbz>0 group by c.cjmc,b.ypdm,b.ypmc,b.ypgg,c.ykdw
12）住院医生开药统计
select a.ysdm,c.name,a.ksdm,e.name,a.ypmc,a.ypgg,avg(a.ypdj), sum(a.ypsl/a.dwxs),a.ypdw,sum(a.zje),1
from ZY_BRFYMXK a (nolock),YY_ZGBMK c (nolock),YK_YPCDMLK d(nolock),YY_KSBMK e where a.zxrq between @ksrq and @jsrq+'24' and a.idm=d.idm and a.ysdm=c.id
and d.py like @py and a.ksdm = e.id
group by a.ypmc,a.ypdw,a.ypgg,a.ksdm,e.name,a.ysdm,c.name
13）门诊病人用药统计
select a.yfdm ,b.ypdm, b.ypmc,b.ypgg, b.ypdw, b.ylsj*b.dwxs/b.ykxs ypdj,
sum(b.ypsl*b.cfts/b.dwxs) ypsl, sum(b.ylsj*b.ypsl*b.cfts/b.ykxs) zje from VW_MZFYZD a(nolock),VW_MZFYMX b(nolock)
where a.fyrq between @ksrq and @jsrq+'24' and a.jlzt=0 and a.jzbz in (1,2)
and a.xh=b.fyxh
group by a.yfdm ,b.ypdm, b.ypmc,b.ypgg, b.ypdw, b.ylsj,b.dwxs,b.ykxs
14）住院病人用药统计
select a.yfdm ,b.ypdm, b.ypmc,b.ypgg, b.ypdw, b.ylsj*b.dwxs/b.ykxs ypdj,
第 30 页 共 53 页