ALTER proc [dbo].[wsp] @wlcode nvarchar(50),--物料编号 @cost int --出库量 as --先得出该货物的库存是否够 declare @spare float --剩余库存 select @spare= sum(inqty)-sum(foutqty) from a074 where materialcode=@wlcode if(@spare>=@cost) begin --根据入库id采用先进先出原则对货物的库存进行处理 update a074 set foutqty= case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<=a.mxid and inqty!=foutqty)>=0 then a.inqty else case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty)<0 then 0 else (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0)+a.foutqty from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty) end end from a074 a where materialcode=@wlcode and inqty!=foutqty end else raiserror('库存不足',16,1) return exec wsp '10厘钻咀' ,14恰好我项目里有个。你那典型的先进先出嘛
我用的是mxid排序,你改成日期就好了,一样的
--先入先出的应用哦。 --参考这个 create table t( id int identity(1,1), name varchar(50),--商品名称 j int, --入库数量 c int, --出库数量 jdate datetime --入库时间 ) insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01' insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07' insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21' insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15' insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03' insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01' insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01' gocreate proc wsp @name varchar(50),--商品名称 @cost int --销售量 as --先得出该货物的库存是否够 declare @spare float --剩余库存 select @spare=sum(j)-sum(c) from t where name=@name if(@spare>=@cost) begin --根据入库日期采用先进先出原则对货物的库存进行处理 update t set c= case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name 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 name=@name 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 name=@name and jdate<a.jdate and j!=c) end end from t a where name=@name and j!=c end else raiserror('库存不足',16,1) return go --测试:exec wsp @name='A',@cost=180 select * from t --drop table t --drop proc wsp
ALTER proc [dbo].[wsp]
@wlcode nvarchar(50),--物料编号
@cost int --出库量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare= sum(inqty)-sum(foutqty) from a074 where materialcode=@wlcode
if(@spare>=@cost)
begin
--根据入库id采用先进先出原则对货物的库存进行处理
update a074 set foutqty=
case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<=a.mxid and inqty!=foutqty)>=0
then a.inqty
else
case when (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0) from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty)<0
then 0
else (select @cost-isnull(sum(inqty),0)+isnull(sum(foutqty),0)+a.foutqty from a074 where materialcode=@wlcode and mxid<a.mxid and inqty!=foutqty)
end
end
from a074 a where materialcode=@wlcode and inqty!=foutqty
end
else
raiserror('库存不足',16,1)
return
exec wsp '10厘钻咀' ,14恰好我项目里有个。你那典型的先进先出嘛
--先入先出的应用哦。
--参考这个
create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name 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 name=@name 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 name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp