发布时间 : 星期四 文章SqlServer 常用命令说明更新完毕开始阅读db348b09581b6bd97f19eada
友恒通有限公司
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 页