if exists(Select Name from sysobjects where Name='RK') Drop table RK if Exists(Select Name From sysobjects where Name='CK') Drop table CKCreate table RK( 入库编号 varchar(10), 入库日期 datetime, 产品编号 varchar(10), 数量 int )Create table CK( 出库编号 varchar(10), 出库日期 datetime, 入库编号 varchar(10), 产品编号 varchar(10), 数量 int ) insert into rk select 'r001','2004-01-01','001',90 union all select 'r002','2004-01-02','001',100 union all select 'r003','2004-01-03','001',100 union all select 'r004','2004-01-04','001',100insert into CK select 'c001','2004-01-02','','001',50 union all select 'c002','2004-01-03','','001',100 union all select 'c003','2004-01-04','r002','001',100 union all select 'c004','2004-01-05','','001',50 union all select 'c005','2004-01-06','r004','001',50 go--select * from rk --select * from ck--Drop proc sumkc if exists(select Name from sysobjects where Name='sumkc') Drop proc sumkc goCreate proc SumKC @Debug int=0 asDeclare @MinCKNo varchar(10) --最小的出库编号 DeClare @LeftNums Numeric(9,2) --还要出库数量 Declare @RKNo varchar(10) --入库编号 Declare @MinMacNo varchar(10) --产品编号 Declare @MinRk varchar(10) --最小的入库编号 Declare @minrkno varchar(10) --最小入库编号 Declare @CKdate datetime --出库日期 --判定库存结构表是否已存在 if exists(select Name from sysobjects where name='Kc') Drop table KC--把入库流水帐写入KC select * into KC from rk--每次取最小的出库编号 Select @minCKNo=min(出库编号) from ckwhile @minckno is not null
Begin if @debug<>0 select @minCKNo-------------------------------------- Declare @Nums numeric (9,2) --取最小的产品编号 select @minmacno=min(产品编号) From ck where 出库编号=@minckno while @minmacno is not null begin Select @rkno=入库编号,@LeftNums=数量,@CKdate=出库日期 from ck where 出库编号=@minckno and 产品编号=@minmacno
if @debug<>0 select @rkno,@LeftNums,@minmacno--------------------- --如果入库编号为空,先进先出 if @rkno is null or @rkno='' --如果剩下要出库的数量大于则继续出库 begin Select @minrkno=min(入库编号) from kc where 产品编号=@minmacno and 入库日期<=@CKdate and 数量>0
while @leftNums>0 and @minrkno is not null begin select @Nums=数量 from kc where 入库编号=@minrkno and 产品编号=@minmacno if @Debug<>0 select @minrkno,@Nums,@LeftNums------------------------------- if @nums-@leftNums<0 --一次入库不够出库 begin select @leftNums=@leftNums-@Nums update kc set 数量=0 where 入库编号=@minrkno and 产品编号=@minmacno Select @minrkno=min(入库编号) from kc where 产品编号=@minmacno and 入库日期<=@CKdate and 数量>0 and 入库编号>@minrkno end else --一次就够出库 begin if @debug<>0 select @minrkno,@Nums,@LeftNums------------------------ update kc set 数量=@Nums-@leftNums where 入库编号=@minrkno and 产品编号=@minmacno set @leftNums=0 end end
--如果入库不够出库 if @leftNums>0 and @minrkno is null begin ---你要的处理程序 return end end
else --入库编号不为空 begin select @Nums=数量-@leftNums from kc where 入库编号=@rkno and 产品编号=@minmacno --select @Nums=@Nums update kc set 数量=@Nums where 入库编号=@rkno and 产品编号=@minmacno end
--取下一条产品编号 select @minmacno=min(产品编号) From ck where 出库编号=@minckno and 产品编号>@minmacno end --取下一条的出库记录 Select @minCKNo=min(出库编号) from ck where 出库编号>@minckno end
Drop table RK
if Exists(Select Name From sysobjects where Name='CK')
Drop table CKCreate table RK(
入库编号 varchar(10),
入库日期 datetime,
产品编号 varchar(10),
数量 int
)Create table CK(
出库编号 varchar(10),
出库日期 datetime,
入库编号 varchar(10),
产品编号 varchar(10),
数量 int
)
insert into rk
select 'r001','2004-01-01','001',90
union all
select 'r002','2004-01-02','001',100
union all
select 'r003','2004-01-03','001',100
union all
select 'r004','2004-01-04','001',100insert into CK
select 'c001','2004-01-02','','001',50
union all
select 'c002','2004-01-03','','001',100
union all
select 'c003','2004-01-04','r002','001',100
union all
select 'c004','2004-01-05','','001',50
union all
select 'c005','2004-01-06','r004','001',50
go--select * from rk
--select * from ck--Drop proc sumkc
if exists(select Name from sysobjects where Name='sumkc')
Drop proc sumkc
goCreate proc SumKC
@Debug int=0
asDeclare @MinCKNo varchar(10) --最小的出库编号
DeClare @LeftNums Numeric(9,2) --还要出库数量
Declare @RKNo varchar(10) --入库编号
Declare @MinMacNo varchar(10) --产品编号
Declare @MinRk varchar(10) --最小的入库编号
Declare @minrkno varchar(10) --最小入库编号
Declare @CKdate datetime --出库日期
--判定库存结构表是否已存在
if exists(select Name from sysobjects where name='Kc')
Drop table KC--把入库流水帐写入KC
select * into KC from rk--每次取最小的出库编号
Select @minCKNo=min(出库编号) from ckwhile @minckno is not null
Begin
if @debug<>0
select @minCKNo--------------------------------------
Declare @Nums numeric (9,2) --取最小的产品编号
select @minmacno=min(产品编号) From ck where 出库编号=@minckno
while @minmacno is not null
begin
Select @rkno=入库编号,@LeftNums=数量,@CKdate=出库日期
from ck where 出库编号=@minckno and 产品编号=@minmacno
if @debug<>0
select @rkno,@LeftNums,@minmacno---------------------
--如果入库编号为空,先进先出
if @rkno is null or @rkno=''
--如果剩下要出库的数量大于则继续出库
begin
Select @minrkno=min(入库编号) from kc where 产品编号=@minmacno
and 入库日期<=@CKdate and 数量>0
while @leftNums>0 and @minrkno is not null
begin
select @Nums=数量 from kc where 入库编号=@minrkno and 产品编号=@minmacno
if @Debug<>0
select @minrkno,@Nums,@LeftNums-------------------------------
if @nums-@leftNums<0 --一次入库不够出库
begin
select @leftNums=@leftNums-@Nums
update kc set 数量=0 where 入库编号=@minrkno and 产品编号=@minmacno
Select @minrkno=min(入库编号) from kc where 产品编号=@minmacno
and 入库日期<=@CKdate and 数量>0 and 入库编号>@minrkno
end
else --一次就够出库
begin
if @debug<>0
select @minrkno,@Nums,@LeftNums------------------------
update kc set 数量=@Nums-@leftNums
where 入库编号=@minrkno and 产品编号=@minmacno
set @leftNums=0
end
end
--如果入库不够出库
if @leftNums>0 and @minrkno is null
begin
---你要的处理程序
return
end
end
else --入库编号不为空
begin
select @Nums=数量-@leftNums from kc where 入库编号=@rkno and 产品编号=@minmacno
--select @Nums=@Nums
update kc set 数量=@Nums where 入库编号=@rkno and 产品编号=@minmacno
end
--取下一条产品编号
select @minmacno=min(产品编号) From ck where 出库编号=@minckno and 产品编号>@minmacno
end
--取下一条的出库记录
Select @minCKNo=min(出库编号) from ck where 出库编号>@minckno
end
Select * from kc
goexec sumkc 0