if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[huizong]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[huizong]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE PROCEDURE huizong @year varchar(4),@month varchar(2),@cWhCode varchar(20),@about varchar(20)
AS
set nocount on declare @temp1 varchar(2000),@temp2 varchar(6),@zhangbu varchar(20) set @temp2=@year+@month Create Table #temp ( -- 汇总表
iIsId int identity (1,1) not null,
cCode varchar(20) not null , -- 商品编号
cName varchar(40) null , -- 商品名称
benyueqichu float default 0 , --本月期初
benyueruku float default 0 , --本月入库
benyuechuku float default 0 , --本月出库
benyueleft float default 0 ) --本月余额 Create Table #temp2 (
iIsId int identity (1,1) not null,
dDate DateTime not null , -- 月结日期
cMth varchar(6) not null , -- 月结年月(200304)
cOrderNo varchar(12) not null , -- 入库单编号
dInDate DateTime null , -- 入库日期
iLineNo int not null , -- 行号
cCaseNo varchar(20) null , -- 案号
cCode varchar(20) not null , -- 商品编号
cName varchar(40) null , -- 商品名称
cStd varchar(30) null , -- 商品规格
cUnit varchar(10) null , -- 计量单位
iQuantity float default 0 , -- 入库数量
iOutQty float default 0 , -- 已出库数量累计
iBalQty float default 0 , -- 库存数量 --------------------
iPrice float default 0 , -- 估价
cWhCode varchar(20) null , -- 仓库编号
cWhName varchar(40) null , -- 仓库名称
cPrdCCode varchar(10) null , -- 商品类别编号
cPrdCName varchar(40) null , -- 商品类别名称
cInType varchar(10) null , -- 入库类别 ( ZK--表示暂扣入库 / FM--表示罚没入库 )
cRe varchar(60) null ) -- 备注
if @about='暂扣'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='ZK' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp (cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname,cUnit
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='ZK' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname,cUnit
update #temp set benyueleft=benyueqichu+benyueruku-benyuechuku
end
if @about='罚没'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='FM' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp(cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='FM' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname
update #temp set benyueleft=benyueqichu+benyueruku-benyuechukuend
if @about='专营专管'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='FM' and cPrdCName='专营专管' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp (cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='FM' and cPrdCName='专营专管' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname
update #temp set benyueleft=benyueqichu+benyueruku-benyuechukuend
select * from #tempGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[huizong]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE PROCEDURE huizong @year varchar(4),@month varchar(2),@cWhCode varchar(20),@about varchar(20)
AS
set nocount on declare @temp1 varchar(2000),@temp2 varchar(6),@zhangbu varchar(20) set @temp2=@year+@month Create Table #temp ( -- 汇总表
iIsId int identity (1,1) not null,
cCode varchar(20) not null , -- 商品编号
cName varchar(40) null , -- 商品名称
benyueqichu float default 0 , --本月期初
benyueruku float default 0 , --本月入库
benyuechuku float default 0 , --本月出库
benyueleft float default 0 ) --本月余额 Create Table #temp2 (
iIsId int identity (1,1) not null,
dDate DateTime not null , -- 月结日期
cMth varchar(6) not null , -- 月结年月(200304)
cOrderNo varchar(12) not null , -- 入库单编号
dInDate DateTime null , -- 入库日期
iLineNo int not null , -- 行号
cCaseNo varchar(20) null , -- 案号
cCode varchar(20) not null , -- 商品编号
cName varchar(40) null , -- 商品名称
cStd varchar(30) null , -- 商品规格
cUnit varchar(10) null , -- 计量单位
iQuantity float default 0 , -- 入库数量
iOutQty float default 0 , -- 已出库数量累计
iBalQty float default 0 , -- 库存数量 --------------------
iPrice float default 0 , -- 估价
cWhCode varchar(20) null , -- 仓库编号
cWhName varchar(40) null , -- 仓库名称
cPrdCCode varchar(10) null , -- 商品类别编号
cPrdCName varchar(40) null , -- 商品类别名称
cInType varchar(10) null , -- 入库类别 ( ZK--表示暂扣入库 / FM--表示罚没入库 )
cRe varchar(60) null ) -- 备注
if @about='暂扣'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='ZK' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp (cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname,cUnit
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='ZK' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname,cUnit
update #temp set benyueleft=benyueqichu+benyueruku-benyuechuku
end
if @about='罚没'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='FM' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp(cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='FM' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname
update #temp set benyueleft=benyueqichu+benyueruku-benyuechukuend
if @about='专营专管'
begin
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)<''+ @temp2 +'' and cInType='FM' and cPrdCName='专营专管' and cWhCode like ''+ @cWhCode+'%'+''
insert into #temp (cCode,cName,benyueqichu)
select cCode,cname,sum(iBalQty) from #temp2 group by cCode,cname
delete #temp2
insert into #temp2(dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe)
select dDate,cMth,cOrderNo,dInDate,iLineNo,cCaseNo,cCode,cName,cStd,cUnit,iQuantity,iOutQty,iBalQty,iPrice,cWhCode,cWhName,cPrdCCode,cPrdCName,cInType,cRe from MthFwd where cMth= ''+ @temp2 +'' and convert(char(6),dindate,112)=''+ @temp2 +'' and cInType='FM' and cPrdCName='专营专管' and cWhCode like ''+ @cWhCode+'%'+''
update #temp set benyueruku=b.benyueruku,benyuechuku=b.benyuechuku from #temp a,(
select ccode,sum(iQuantity) benyueruku,sum(iOutQty) benyuechuku from #temp2 group by ccode) b where a.ccode=b.ccode
delete #temp2 where cCode in (select ccode from #temp)
insert into #temp (cCode,cName,benyueruku,benyuechuku)
select cCode,cname,sum(iQuantity),sum(iOutQty)from #temp2 group by cCode,cname
update #temp set benyueleft=benyueqichu+benyueruku-benyuechukuend
select * from #tempGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
- CASE WHEN 條件1 THEN '+' WHEN 條件2 THEN '-' ELSE '-' END
- 一个触发器的问题,高手请进!急
- sql还原问题
- zjcxc(邹建),进来帮小弟看看怎么用SQL语句实现
- 如果在搜索的结果包括搜索的关键字前后50字,并去掉html标记,不能用管理员专用函数
- 那里有DTS下载。我的2000 4IN1 光盘里没有 或者谁有的,发一个过来啊
- 请教一个查询的问题
- 请帮助完成下面的fuction
- 数据库无法备份,求助啊
- 请教,sql server 性能的问题。
- 急!SQL SERVER2000中认证连接问题
- 在sql的企业管理器中建立一个角色,并赋予了一些权限,如何得到以上操作的sql脚本呢?
可是不能分一级,和二级科目现实
大家帮我改一改
二级科目为:0101
0102
0103
........
三级科目为:010101
010102
010103
.........你想得到什么结果??