sql 先进先出 库存

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 '  
go

create   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 = 110  
select   *   from  t


-- drop table t 
-- drop proc wsp

/*

(4 行受影响) 
id          name                                               j           c           jdate 
----------- -------------------------------------------------- ----------- ----------- ----------------------- 
1           A                                                  100         100         2007-12-01 00:00:00.000 
2           A                                                  200         200         2008-01-07 00:00:00.000 
3           B                                                  320         0           2007-12-21 00:00:00.000 
4           A                                                  100         30          2008-01-15 00:00:00.000 
5           B                                                  90          0           2008-02-03 00:00:00.000 
6           A                                                  460         0           2008-02-01 00:00:00.000 
7           A                                                  510         0           2008-03-01 00:00:00.000

(7 行受影响) 
*/















CREATE   TABLE  #tmp 
  ( ID  int   IDENTITY  ( 1 ,  1 ),  
  单价  decimal ( 18 ,  2 )  NOT   NULL  ,  
  进库数量  decimal ( 18 ,  0 )  NOT   NULL  ,  
  已出数量  decimal ( 18 ,  0 )  NOT   NULL  
  ) 
   insert   into  #tmp(单价,进库数量,已出数量)  values ( 1.1 , 50 , 0 ) 
   insert   into  #tmp(单价,进库数量,已出数量)  values ( 1.3 , 30 , 0 ) 
   insert   into  #tmp(单价,进库数量,已出数量)  values ( 1.4 , 60 , 0 ) 
   insert   into  #tmp(单价,进库数量,已出数量)  values ( 1.5 , 20 , 0 ) 
  
      
   select   *   from  #tmp  
   
   
   declare   @t   decimal ( 18 ,  0 )  -- 一次出库数量 
, @temp   decimal ( 18 ,  0 )  -- 某一单价的临时出库数量 
   select   @t = 20  
   update  #tmp  set   @temp =  
   case   when   @t > 进库数量 - 已出数量 
   then  进库数量 - 已出数量  -- 当出库数量大于某一单价的结存数量时，那么此单价的出库数量就是结存数量，也就是说此单价的库存数量全部消耗完。 
   else   @t   -- 出库数量小于或等于某一单价的结存数量时，那么此次的出库数量就是实际的出库数量 
   end , 
    @t = @t - @temp , -- 减去一次针对某个单价的临时出库数量 
   已出数量 = @temp + 已出数量  -- 新出库的数量+以前出库的数量 
    where  已出数量 <> 进库数量  -- 某个单价出库完了就不参于出库计算，即结存数为零的就排除在外 
    select   *   from  #tmp 
    
    
set   @t = 40  
    update  #tmp  set   @temp =  
    case   when   @t > 进库数量 - 已出数量 
    then  进库数量 - 已出数量 
    else   @t  
    end , 
    @t = @t - @temp , 
   已出数量 = @temp + 已出数量 
    where  已出数量 <> 进库数量 
   
    select   *   from  #tmp 
   
    go  
    drop   table  #tmp