按仓库号实现先进先出问题!!!!在线
create table inDetail(AutoRkID varchar(20),ProductID varchar(20),inQty int,nowQty int,storageID int,inPrice dec(10,2),inDate datetime)
insert inDetail select 'RK20070131-9 ', 'ddj001 ',50,50,1,200.50, '2007-01-31 16:18:34.187 '
union all select 'RK20070131-9 ', 'zhuanz001 ',100,100,1,20.36, '2007-01-31 16:18:34.187 '
union all select 'RK20070131-10 ', 'ddj001 ',50,50,2,200.59, '2007-01-31 16:20:49.123 '
union all select 'RK20070131-10 ', 'huotao001 ',200,200,2,14.00, '2007-01-31 16:20:49.123 '
union all select 'RK20070131-11 ', 'ddj001 ',80,80,1,200.80, '2007-01-31 16:25:33.607 '
union all select 'RK20070131-12 ', 'ddj001 ',200,200,1,200.86, '2007-01-31 16:25:53.403 '
修改如下:
update
inDetail
set nowQty=case when (select isnull(sum(inQty),0) from inDetail a where a.ProductID= 'ddj001 ' and a.inDate <=inDetail.inDate) <140
then 0
else
case when inQty-(140-(select isnull(sum(inQty),0) from inDetail a where a.ProductID= 'ddj001 ' and a.inDate <inDetail.inDate)) <inQty
then inQty-(140-(select isnull(sum(inQty),0) from inDetail a where a.ProductID= 'ddj001 ' and a.inDate <inDetail.inDate))
else inQty
end
end
from inDetail
where ProductID= 'ddj001 '
select * from inDetail
drop table inDetail
怎么修改上面存储过程按仓库号先进先出得到如下结果:
AutoRkID ProductID inQty nowQty storageID inPrice inDate
-------------------- -------------------- ----------- ----------- ------------ ------------------------------
RK20070131-9 ddj001 50 0 1 200.50 2007-01-31 16:18:34.187
RK20070131-9 zhuanz001 100 100 1 20.36 2007-01-31 16:18:34.187
RK20070131-10 ddj001 50 50 2 200.59 2007-01-31 16:20:49.123
RK20070131-10 huotao001 200 200 2 14.00 2007-01-31 16:20:49.123
RK20070131-11 ddj001 80 0 1 200.80 2007-01-31 16:25:33.607
RK20070131-12 ddj001 200 190 1 200.86 2007-01-31 16:25:53.403
（所影响的行数为 6 行）
[解决办法]
--try:
create table inDetail(AutoRkID varchar(20),ProductID varchar(20),inQty int,nowQty int,storageID int,inPrice dec(10,2),inDate datetime)
insert inDetail select 'RK20070131-9 ', 'ddj001 ',50,50,1,200.50, '2007-01-31 16:18:34.187 '
union all select 'RK20070131-9 ', 'zhuanz001 ',100,100,1,20.36, '2007-01-31 16:18:34.187 '
union all select 'RK20070131-10 ', 'ddj001 ',50,50,2,200.59, '2007-01-31 16:20:49.123 '
union all select 'RK20070131-10 ', 'huotao001 ',200,200,2,14.00, '2007-01-31 16:20:49.123 '
union all select 'RK20070131-11 ', 'ddj001 ',80,80,1,200.80, '2007-01-31 16:25:33.607 '
union all select 'RK20070131-12 ', 'ddj001 ',200,200,1,200.86, '2007-01-31 16:25:53.403 '
/*按storageID先進先出*/
declare @out int,@productID varchar(30)
select @out=140, @productID= 'ddj001 '
update A
set nowQty=case when isnull((select sum(nowQty) from inDetail C where C.ProductID=@productID and C.storageID <=A.storageID),0)> =@out
and isnull((select sum(nowQty) from inDetail C where C.ProductID=@productID and C.storageID <A.storageID),0) <@out
then case when isnull((select sum(nowQty) from inDetail B where B.ProductID=@productID and (B.storageID <A.storageID or (B.inDate <A.inDate and B.storageID=A.storageID))),0)> =@out
then nowQty
else case when isnull((select sum(nowQty) from inDetail B where B.ProductID=@productID and (B.storageID <A.storageID or (B.inDate <=A.inDate and B.storageID=A.storageID))),0)> =@out
then isnull((select sum(nowQty) from inDetail B where B.ProductID=@productID and (B.storageID <A.storageID or (B.inDate <=A.inDate and B.storageID=A.storageID))),0)-@out
else 0 end
end
when isnull((select sum(nowQty) from inDetail D where D.ProductID=@productID and D.storageID <A.storageID),0)> =@out
then nowQty
when isnull((select sum(nowQty) from inDetail C where C.ProductID=@productID and C.storageID <=A.storageID),0) <=@out
then 0
end
from inDetail A
where A.ProductID=@productID
--
select * from inDetail
/*
AutoRkID ProductID inQty nowQty storageID inPrice inDate
-----------------------------------------------
RK20070131-9 ddj001 50 0 1 200.50 2007-01-31 16:18:34.187
RK20070131-9 zhuanz001 100 100 1 20.36 2007-01-31 16:18:34.187
RK20070131-10 ddj001 50 50 2 200.59 2007-01-31 16:20:49.123
RK20070131-10 huotao001 200 200 2 14.00 2007-01-31 16:20:49.123
RK20070131-11 ddj001 80 0 1 200.80 2007-01-31 16:25:33.607
RK20070131-12 ddj001 200 190 1 200.86 2007-01-31 16:25:53.403
*/
drop table inDetail
[解决办法]
Create Table inDetail(AutoRkID Varchar(20),ProductID Varchar(20),inQty Int,nowQty Int,storageID Int,inPrice Dec(10,2),inDate Datetime)
Insert inDetail Select 'RK20070131-9 ', 'ddj001 ',50,50,1,200.50, '2007-01-31 16:18:34.187 '
Union All Select 'RK20070131-9 ', 'zhuanz001 ',100,100,1,20.36, '2007-01-31 16:18:34.187 '
Union All Select 'RK20070131-10 ', 'ddj001 ',50,50,2,200.59, '2007-01-31 16:20:49.123 '
Union All Select 'RK20070131-10 ', 'huotao001 ',200,200,2,14.00, '2007-01-31 16:20:49.123 '
Union All Select 'RK20070131-11 ', 'ddj001 ',80,80,1,200.80, '2007-01-31 16:25:33.607 '
Union All Select 'RK20070131-12 ', 'ddj001 ',200,200,1,200.86, '2007-01-31 16:25:53.403 '
GO
Create Procedure SP_UpdatenowQty(@out Int,@productID Varchar(30), @storageID Int)
As
Begin
Update
T1
Set
nowQty = (Case When T1.inQty + T3.SUMinQty < @out Then 0
Else T1.inQty + T3.SUMinQty - @out End)
From
inDetail T1
Inner Join
(Select *, IsNull((Select SUM(inQty) From inDetail Where ProductID = T2.ProductID And storageID = T2.storageID And inDate <T2.inDate ), 0) As SUMinQty From inDetail T2) T3
On T1.ProductID = T3.ProductID And T1.storageID = T3.storageID And T1.AutoRkID = T3.AutoRkID
Where T3.SUMinQty - @out <= 0 And T1.ProductID = @productID And T1.storageID = @storageID
End
GO
EXEC SP_UpdatenowQty 140, 'ddj001 ', 1
Select * From inDetail
GO
Drop Table inDetail
Drop Procedure SP_UpdatenowQty
/*
AutoRkIDProductIDinQtynowQtystorageIDinPriceinDate
RK20070131-9ddj0015001200.502007-01-31 16:18:34.187
RK20070131-9zhuanz001100100120.362007-01-31 16:18:34.187
RK20070131-10ddj00150502200.592007-01-31 16:20:49.123
RK20070131-10huotao001200200214.002007-01-31 16:20:49.123
RK20070131-11ddj0018001200.802007-01-31 16:25:33.607
RK20070131-12ddj0012001901200.862007-01-31 16:25:53.403
*/
