日期:2014-05-18  浏览次数:20396 次

按仓库号实现先进先出问题!!!!在线
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