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'
怎么实现存储过程,对按某一仓库号的商品的先进先出
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'
怎么实现存储过程,对按某一仓库号的商品的先进先出
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', 1Select * From inDetail
GO
Drop Table inDetail
Drop Procedure SP_UpdatenowQty
/*
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
*/