发布时间 : 星期三 文章SqlServer 常用命令说明更新完毕开始阅读
友恒通有限公司
select *
from VW_MZBRJSK (nolock)
where sfrq between @ksrq and @jsrq+\
注意:
日期类型: ut_rq8 8位字符 格式：yyyymmdd
ut_rq16 16位字符 格式：yyyymmddhh:nn:ss
12）关于纵表转横表的写法
select a.sjh,a.cardno,a.hzxm,xjje,zpje,srje,yhje,zje,qkbz,qkje,
convert(numeric(12,2),sum(case when b.lx in ('01') then je else 0 end)) dnzhzf,
convert(numeric(12,2),sum(case when b.lx in ('02','04','07') then je else 0 end)) lnzhzf, convert(numeric(12,2),sum(case when b.lx in ('03','05','08') then je else 0 end)) xjzf1, convert(numeric(12,2),sum(case when b.lx in ('06') then je else 0 end)) tczf, convert(numeric(12,2),sum(case when b.lx in ('09') then je else 0 end)) fjzf
into #temp1
from VW_MZBRJSK a (nolock),VW_MZJEMXK b (nolock) where a.sfrq between '20031201' and '2003120124' and a.ybjszt=2
and a.jlzt in (0,1,2) and a.sjh=b.jssjh
group by a.sjh,a.cardno,a.hzxm,xjje,zpje,srje,yhje,zje,qkbz,qkje
select a.cardno,a.hzxm,
convert(numeric(12,2),sum(dnzhzf)) 当年账户支付, convert(numeric(12,2),sum(lnzhzf)) 历年账户支付, convert(numeric(12,2),sum(xjzf1)) 现金支付1, convert(numeric(12,2),sum(tczf)) 统筹支付, convert(numeric(12,2),sum(fjzf)) 附加支付, convert(numeric(12,2),sum(xjje)) 现金支付, convert(numeric(12,2),sum(zpje)) 支票支付,
convert(numeric(12,2),sum(case when qkbz=1 then qkje else 0 end)) 其他记帐, convert(numeric(12,2),sum(case when qkbz=2 then qkje else 0 end)) 欠款,
convert(numeric(12,2),sum(case when qkbz=3 then qkje else 0 end)) 充值卡支付, convert(numeric(12,2),sum(srje)) 舍入, convert(numeric(12,2),sum(yhje)) 优惠, convert(numeric(12,2),sum(zje)) 总金额 into #temp2 from #temp1 a
group by a.cardno,a.hzxm
注意：
1）纵表转横表的写法
2）要了解#temp1中为何加sjh
第 25 页 共 53 页
友恒通有限公司
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 页