仓库货品收发函数分析

仓库货品收发存函数报表 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 行)
*/

阅读更多
个人分类: SQL-Server
想对作者说点什么? 我来说一句

仓库收发存模版

2012年11月04日 18KB 下载

仓库货品管理课程设计

2010年12月23日 355KB 下载

仓库货品管理系统程序设计

2010年12月28日 124KB 下载

仓库管理系统+数据库

2018年01月31日 19.1MB 下载

一套完整的仓库管理系统

2010年12月30日 5.17MB 下载

CAISO dt900 930 940 程式

2013年05月28日 44KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭