SqlServer 常用命令说明 联系客服

友恒通有限公司

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 页

友恒通有限公司

sum(b.ypsl*isnull(b.cfts,1)/b.dwxs) ypsl,

sum(b.ylsj*b.ypsl*isnull(b.cfts,1)/b.ykxs,2) zje from VW_ZYFYZD a (nolock),VW_ZYFYMX b (nolock)

where a.fyrq between @ksrq and @jsrq+'24' 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

15)门诊药房收方工作量统计

select a.yfdm,sfczry, count(distinct b.xh) cfs,

sum(c.ypsl*c.ts*c.cfts*c.ylsj/c.ykxs) je

from VW_MZFYZD a (nolock),VW_MZCFK b (nolock),VW_MZCFMXK c (nolock)

where a.sfrq between @ksrq and @jsrq +'24' and a.jlzt=0 and a.jzbz in (1,2)

and a.cfxh=b.xh and c.cfxh=b.xh group by a.yfdm,sfczry

16)门诊发药工作量统计

select a.yfdm,fyczry, count(distinct b.xh) cfs,

sum(c.ypsl*c.ts*c.cfts*c.ylsj/c.ykxs) je

from VW_MZFYZD a (nolock),VW_MZCFK b (nolock),VW_MZCFMXK c (nolock)

where a.sfrq between @ksrq and @jsrq +'24' and a.jlzt=0 and a.jzbz in (1,2)

and a.cfxh=b.xh and c.cfxh=b.xh group by a.yfdm,fyczyh

17)按住院操作员统计押金和收退款

select a.czyh,a.czym,sum(convert(numeric(12,2),jje-dje)) yjj from ZYB_BRYJK a (nolock)

where a.lrrq between @ksrq and @jsrq+'24'

and a.czlb in (0,1,2,6) --预交金、收付金额 and a.zffs in ('1','3') --现金、财退现金 group by a.czyh,a.czym

第二节 制作报表模板

进入模板后,显示如下内容:

第 31 页 共 53 页

友恒通有限公司

一、页面设置

在file->page setup

? Printer页面选择使用的打印机/文档名/打印份数/打印方向,Printer

选项中最好选择“Default”,可以使报表在不同的打印机下可以正确打印。

2)Paper Size页面设置纸张类型/高度和宽度/纸张方向。如果在Windows

2000的系统下使用Custom纸张类型,需要在打印机设置中的“服务器属性”中创建自定义的纸张。

3)Layout页面设置分栏打印的栏数、栏宽、栏间距、分栏时从上到下还是

从左到右显示内容。

这种情况一般是住院发票大项目太多,不能全部打印,目前系统是默认两栏,如果太多的话,就需要打三栏,或者是收费、挂号结帐单有些医院为了节约纸张需要分栏等类似情况。

4)Margins页面设置上下左右边距,打印纸张的大小单位是英寸还是毫米是

在“Report”菜单下的“Units”中设置。

二、快速制表法

第一节 先进行page setup设置

第二节 在file->new选择report wizard 第三节 选择结果集中的字段

第四节 是否分组?如果要分组设置按什么分组,选择输出格式 第五节 选择字体

三、手工制表法

第六节 先进行page setup设置

第七节 选择file->new report ,显示如下:

第 32 页 共 53 页