仓库货品收发存函数报表 MSSQL2000
VI_INSTORE ---进仓单
BILLID , STOREID , GOODSID , BILLDATE , BILLCODE , QTY , REMARK1
单据ID , 仓库 ， 货品 , 日期 , 单号 , 数量 , 备注
1001 , 原材料仓 , G001 , 2010-05-01 , IN001 , 100 , AB
1002 , 原材料仓 ， G002 ，2010-05-03 ， IN002 ， 300 ， C
1003 ， 原材料仓 , G003 , 2010-05-03 , IN003 , 200 , D
1003 , 原材料仓 , G003 , 2010-05-03 , IN003 , 70 , E
1004 , 原材料仓 , G003 ，2010-05-30 ， IN009 ， 40 ， F
1005 ， 半成品仓 ， G004 ，2010-05-30 ， IN008 ， 33 ， K
VI_OUTSTORE ---出仓单
BILLID , STOREID , GOODSID , BILLDATE , BILLCODE , QTY , REMARK1
单据ID , 仓库 ， 货品 , 日期 , 单号 , 数量 , 备注
3001 , 原材料仓 , G001 , 2010-05-02 , OU001 , 30 ,
3002 , 原材料仓 ， G003 ，2010-05-05 ， OU002 ， 100 ，
3003 ， 半成品仓 , G004 , 2010-05-30 , OU003 , 30 ,
求函数VINSTORE(@FROMDATE, @TODATE) 查询日期('2010-05-01' , '2010-05-31')时结果如下：
请注意期初及结存
STOREID , BILLDATE , GOODSID , BILLCODE , REMARK1 , SQT , IQT , OUQT , CQT
仓库 , 日期 ，货品 ， 单号 ， 备注 ， 期初, 收入 , 发出 , 结存
原材料仓 , 2010-05-01 , G001 , , 期初 , 0 , 0 , 0 , 0
原材料仓 , 2010-05-01 , G001 , IN001 , AB , 0 , 100 , 0 , 100
原材料仓 , 2010-05-02 , G001 , OU001 , , 100 , 0 , 30 , 70
原材料仓 , 2010-05-31 , G001 , , 结存 , 0 , 100 , 30 , 70
原材料仓 , 2010-05-01 , G002 , , 期初 , 0 , 0 , 0 , 0
原材料仓 , 2010-05-03 , G002 , IN002 , C , 0 , 300 , 0 , 300
原材料仓 , 2010-05-31 , G002 , , 结存 , 0 , 300 , 0 , 300
原材料仓 , 2010-05-01 , G003 , , 期初 , 0 , 0 , 0 , 0
原材料仓 , 2010-05-03 , G003 , IN003 , D , 0 , 200 , 0 , 200
原材料仓 , 2010-05-03 , G003 , IN003 , E , 200 , 70 , 0 , 270
原材料仓 , 2010-05-05 , G003 , OU002 , , 270 , 0 , 100 , 170
原材料仓 , 2010-05-30 , G003 , IN009 , F , 170 , 40 , 0 , 210
原材料仓 , 2010-05-01 , G003 , , 结存 , 0 , 310 , 100 , 210
半成品仓 , 2010-05-01 , G004 , , 期初 , 0 , 0 , 0 , 0
半成品仓 , 2010-05-30 , G004 , IN008 , k , 0 , 33 , 0 , 33
半成品仓 , 2010-05-30 , G004 , OU003 , , 33 , 0 , 30 , 3
半成品仓 , 2010-05-01 , G004 , , 结存 , 0 , 33 , 30 , 3
烦请测试 查询日期('2010-05-03' , '2010-05-31') 和 查询日期('2010-06-01' , '2010-06-30')
CREATE TABLE VI_INSTORE(
BILLID INT,
STOREID VARCHAR(30),
GOODSID VARCHAR(30),
BILLDATE DATETIME,
BILLCODE VARCHAR(30),
QTY INT,
REMARK1 VARCHAR(100))
Go
CREATE TABLE VI_OUTSTORE
(BILLID INT,
STOREID VARCHAR(30),
GOODSID VARCHAR(30),
BILLDATE DATETIME,
BILLCODE VARCHAR(30),
QTY INT,
REMARK1 VARCHAR(100))
Go
INSERT VI_INSTORE SELECT '1001','原材料仓','G001','2010-05-01','IN001','100','AB'
UNION ALL SELECT '1002','原材料仓','G002','2010-05-03 ','IN002','300','C'
union ALL SELECT '1003','原材料仓','G003','2010-05-03','IN003','200','D'
union ALL SELECT '1003','原材料仓','G003','2010-05-03','IN003','70','E'
union all SELECT '1004','原材料仓','G003','2010-05-30','IN009','40','F'
union ALL SELECT '1005','半成品仓','G004','2010-05-30','IN008','33','K'
Go
INSERT VI_OUTSTORE SELECT 3001,'原材料仓','G001','2010-05-02','OU001',30,'aa'
union ALL SELECT 3002,'原材料仓','G003','2010-05-05','OU002',100,'bb'
union ALL SELECT 3003,'半成品仓','G004','2010-05-30','OU003',30,'cc'
GO
--研究了很久,发现如果不用两层游标真的是解决不了你这个问题
if object_id('VINSTORE') is not null drop function VINSTORE
go
create function VINSTORE(@fromdate DATETIME,@todate DATETIME)
returns @t table (STOREID varchar(30), BILLDATE datetime, GOODSID varchar(10), BILLCODE varchar(10), REMARK1 varchar(10),
SQT int, IQT int, OUQT int, CQT int)
as
begin
declare @STOREID VARCHAR(30)
declare @GOODSID VARCHAR(30)
declare @BILLDATE DATETIME
declare @BILLCODE VARCHAR(30)
declare @IQT INT
declare @OUQT INT
declare @REMARK1 VARCHAR(100)
declare @CQT int
declare cur_o cursor for select distinct STOREID,GOODSID from VI_INSTORE
open cur_o
fetch next from cur_o into @STOREID,@GOODSID
while @@fetch_status=0
begin
--插入期初
set @IQT=isnull((select sum(QTY) from VI_INSTORE where BILLDATE<@fromdate and STOREID =@STOREID and GOODSID=@GOODSID),0)
set @OUQT=isnull((select sum(QTY) from VI_OUTSTORE where BILLDATE<@fromdate and STOREID =@STOREID and GOODSID=@GOODSID),0)
set @CQT=@IQT-@OUQT
insert @t
select @STOREID ,@fromdate ,@GOODSID , '' ,'期初' , 0 ,@IQT ,@OUQT ,@CQT
--插入查询范围的数据
declare cur_i cursor for select * from (
select BILLDATE , BILLCODE , REMARK1 ,QTY IQT,0 OUQT from VI_INSTORE
where BILLDATE between @fromdate and @todate and STOREID =@STOREID and GOODSID=@GOODSID
union all
select BILLDATE , BILLCODE , REMARK1 ,0 IQT,QTY OUQT from VI_OUTSTORE
where BILLDATE between @fromdate and @todate and STOREID =@STOREID and GOODSID=@GOODSID)t
order by BILLDATE
open cur_i
fetch next from cur_i into @BILLDATE,@BILLCODE,@REMARK1,@IQT,@OUQT
while @@fetch_status=0
begin
insert @t
select @STOREID ,@BILLDATE ,@GOODSID , @BILLCODE ,@REMARK1 ,
@CQT SQT ,@IQT IQT ,@OUQT OUQT ,@CQT+@IQT-@OUQT CQT
set @CQT=@CQT+@IQT-@OUQT
fetch next from cur_i into @BILLDATE,@BILLCODE,@REMARK1,@IQT,@OUQT
end
close cur_i
deallocate cur_i
--插入结存
set @IQT=isnull((select sum(QTY) from VI_INSTORE where BILLDATE<=@todate and STOREID =@STOREID and GOODSID=@GOODSID),0)
set @OUQT=isnull((select sum(QTY) from VI_OUTSTORE where BILLDATE<=@todate and STOREID =@STOREID and GOODSID=@GOODSID),0)
set @CQT=@IQT-@OUQT
insert @t
select @STOREID ,@fromdate ,@GOODSID , '' ,'结存' , 0 ,@IQT ,@OUQT ,@CQT
fetch next from cur_o into @STOREID,@GOODSID
end
close cur_o
deallocate cur_o
return
end
--测试1 查询日期('2010-05-01' , '2010-05-31')时结果如下
select STOREID,convert(varchar(10), BILLDATE,23) BILLDATE,GOODSID,BILLCODE,REMARK1,SQT,IQT,OUQT,CQT
from dbo.VINSTORE('2010-05-01','2010-05-31')
order by GOODSID
/*
STOREID BILLDATE GOODSID BILLCODE REMARK1 SQT IQT OUQT CQT
------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
原材料仓 2010-05-01 G001 期初 0 0 0 0
原材料仓 2010-05-01 G001 IN001 AB 0 100 0 100
原材料仓 2010-05-02 G001 OU001 aa 100 0 30 70
原材料仓 2010-05-01 G001 结存 0 100 30 70
原材料仓 2010-05-01 G002 期初 0 0 0 0
原材料仓 2010-05-03 G002 IN002 C 0 300 0 300
原材料仓 2010-05-01 G002 结存 0 300 0 300
原材料仓 2010-05-01 G003 期初 0 0 0 0
原材料仓 2010-05-03 G003 IN003 D 0 200 0 200
原材料仓 2010-05-03 G003 IN003 E 200 70 0 270
原材料仓 2010-05-05 G003 OU002 bb 270 0 100 170
原材料仓 2010-05-30 G003 IN009 F 170 40 0 210
原材料仓 2010-05-01 G003 结存 0 310 100 210
半成品仓 2010-05-01 G004 期初 0 0 0 0
半成品仓 2010-05-30 G004 IN008 K 0 33 0 33
半成品仓 2010-05-30 G004 OU003 cc 33 0 30 3
半成品仓 2010-05-01 G004 结存 0 33 30 3
（所影响的行数为 17 行）
*/
--测试2 查询日期('2010-05-03' , '2010-05-31')时结果如下
select STOREID,convert(varchar(10), BILLDATE,23) BILLDATE,GOODSID,BILLCODE,REMARK1,SQT,IQT,OUQT,CQT
from dbo.VINSTORE('2010-05-03','2010-05-31')
order by GOODSID
/*
STOREID BILLDATE GOODSID BILLCODE REMARK1 SQT IQT OUQT CQT
------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
原材料仓 2010-05-03 G001 期初 0 100 30 70
原材料仓 2010-05-03 G001 结存 0 100 30 70
原材料仓 2010-05-03 G002 期初 0 0 0 0
原材料仓 2010-05-03 G002 IN002 C 0 300 0 300
原材料仓 2010-05-03 G002 结存 0 300 0 300
原材料仓 2010-05-03 G003 期初 0 0 0 0
原材料仓 2010-05-03 G003 IN003 D 0 200 0 200
原材料仓 2010-05-03 G003 IN003 E 200 70 0 270
原材料仓 2010-05-05 G003 OU002 bb 270 0 100 170
原材料仓 2010-05-30 G003 IN009 F 170 40 0 210
原材料仓 2010-05-03 G003 结存 0 310 100 210
半成品仓 2010-05-03 G004 期初 0 0 0 0
半成品仓 2010-05-30 G004 IN008 K 0 33 0 33
半成品仓 2010-05-30 G004 OU003 cc 33 0 30 3
半成品仓 2010-05-03 G004 结存 0 33 30 3
（所影响的行数为 15 行）
*/
--测试3 查询日期('2010-06-01' , '2010-06-30')时结果如下
select STOREID,convert(varchar(10), BILLDATE,23) BILLDATE,GOODSID,BILLCODE,REMARK1,SQT,IQT,OUQT,CQT
from dbo.VINSTORE('2010-06-01','2010-06-30')
order by GOODSID
/*
STOREID BILLDATE GOODSID BILLCODE REMARK1 SQT IQT OUQT CQT
------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- ----------- -----------
原材料仓 2010-06-01 G001 期初 0 100 30 70
原材料仓 2010-06-01 G001 结存 0 100 30 70
原材料仓 2010-06-01 G002 期初 0 300 0 300
原材料仓 2010-06-01 G002 结存 0 300 0 300
原材料仓 2010-06-01 G003 期初 0 310 100 210
原材料仓 2010-06-01 G003 结存 0 310 100 210
半成品仓 2010-06-01 G004 期初 0 33 30 3
半成品仓 2010-06-01 G004 结存 0 33 30 3
（所影响的行数为 8 行）
*/