create table t(
id int identity(1,1),
mz varchar(50),--煤种
lc varchar(50),--料场
fq varchar(50),--分区
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC1','FQ1',100,0,'2019-02-01'
insert into t(mz,lc,fq,j,c,jdate) select 'B','LC1','FQ2',140,0,'2019-07-01'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ2',150,0,'2019-08-11'
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC2','FQ3',300,0,'2019-07-11'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ1',300,0,'2019-07-02'
insert into t(mz,lc,fq,j,c,jdate) select 'A','LC2','FQ1',320,0,'2019-06-30'
insert into t(mz,lc,fq,j,c,jdate) select 'B','LC1','FQ2',160,0,'2019-06-15'
insert into t(mz,lc,fq,j,c,jdate) select 'C','LC1','FQ1',170,0,'2019-06-12'
go
alter proc uto.wsp
@mz varchar(50),--煤种
@lc varchar(50),--料场
@fq varchar(50),--分区
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where mz=@mz AND lc=@lc AND fq=@fq
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where mz=@mz AND lc=@lc AND fq=@fq and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where mz=@mz AND lc=@lc AND fq=@fq and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where mz=@mz AND lc=@lc AND fq=@fq and jdate<a.jdate and j!=c)
end
end
from t a where mz=@mz AND lc=@lc AND fq=@fq and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:
exec uto.wsp @mz='C',@lc='LC1',@fq='FQ1',@cost=10
select * from t ORDER BY jdate
drop table t
--drop proc uto.wsp
1 A LC1 FQ1 100 100 2019-02-01 00:00:00.000
8 C LC1 FQ1 170 170 2019-06-12 00:00:00.000
7 B LC1 FQ2 160 0 2019-06-15 00:00:00.000
6 A LC2 FQ1 320 0 2019-06-30 00:00:00.000
2 B LC1 FQ2 140 0 2019-07-01 00:00:00.000
5 C LC1 FQ1 300 300 2019-07-02 00:00:00.000
4 A LC2 FQ3 300 0 2019-07-11 00:00:00.000
3 C LC1 FQ2 150 0 2019-08-11 00:00:00.000
每次结果会增加
来源:http://outofmemory.cn/code-snippet/4515/kucun-xianjin-xianchu-jiandan-example