出库
数据库入库表inDetail  
AutoRkID(入库单号）ProductID(产品编号)inQty(数量)   nowQty(剩余数量）   inPrice(入库价格)     inDate(入库时间）
Rk070125-3     ddj001     50         50         200.50               2007-1-25
Rk070125-6     rkz005     40         40         4000.00             2007-1-25
Rk070125-3     ddj001     80         80         200.35               2007-1-26
Rk070125-3     ddj001     70         70         200.58               2007-1-27
------------------------------------------------------
出库ddj001的商品70件后更新入库表为
Rk070125-3     ddj001     50         0           200.50               2007-1-25
Rk070125-6     rkz005     40         40         4000.00             2007-1-25
Rk070125-3     ddj001     80         60         200.35               2007-1-26
Rk070125-3     ddj001     70         70         200.58               2007-1-27
怎么写存储过程?


------解决方案--------------------
create table indetail
(
AutoRkID varchar(10),--(入库单号）
ProductID varchar(7),---(产品编号)
inQty int,--(数量)
nowQty int,--(剩余数量）
inPrice decimal(10,2),--(入库价格)
inDate datetime --(入库时间）
)

insert into indetail select 'Rk070125-3 ', 'ddj001 ', 50, 50, 200.50, '2007-1-25 '
insert into indetail select 'Rk070125-6 ', 'rkz005 ', 40, 40, 4000.00, '2007-1-25 '
insert into indetail select 'Rk070125-3 ', 'ddj001 ', 80, 80, 200.35, '2007-1-26 '
insert into indetail select 'Rk070125-3 ', 'ddj001 ', 70, 70, 200.58, '2007-1-27 '

--语句
declare @count int
set @count = 70

update t1 set nowqty = (case when t2.sumqty2 <= @count then 0
when t2.sumqty1 < @count and t2.sumqty2 > @count then t2.sumqty2 - @count
else t2.nowqty end)
from indetail t1 ,
(select autorkid,productid,inqty,nowqty,inprice,indate,
sumqty1 = (select isnull(sum(nowqty),0) from indetail where autorkid = a.autorkid and productid = a.productid and indate < a.indate),
sumqty2 = (select sum(nowqty) from indetail where autorkid = a.autorkid and productid = a.productid and indate <= a.indate)
from indetail a
)t2
where t1.autorkid = t2.autorkid and t1.productid = t2.productid and t1.inprice = t2.inprice
and t2.productid = 'ddj001 '

--结果
select * from indetail
Rk070125-3 ddj001 50 0 200.50 2007-01-25 00:00:00.000
Rk070125-6 rkz005 40 40 4000.00 2007-01-25 00:00:00.000
Rk070125-3 ddj001 80 60 200.35 2007-01-26 00:00:00.000
Rk070125-3 ddj001 70 70 200.58 2007-01-27 00:00:00.000


--楼主给出的数据同一个单号还能有两个同样的商品吗?价格即使不一样了,入库时间还可以在两天吗?
如果可能的话,那可能不可能同一个单号中有两种同样的商品,价格不一样,但是入库时间还一样的呢?
如果可以,那么这个语句还需要更该一下
替嘛每次发表提问和回复都需要填写验证码