先进先出（FIFO）

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



库龄分析-先进先出法 

if OBJECT_ID('tx') is not null
begin
    drop table Tx 
end
create table Tx(
id int identity not null,
bflag bit,
ddate datetime,
ccode varchar(3),
cinvcode varchar(3),
qty int
);

insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(1,'2018-5-01','RK1','A',1000)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(1,'2018-5-08','RK2','A',1000)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(1,'2018-5-10','RK3','A',1000)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(1,'2018-5-15','RK4','A',1000)

insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(0,'2018-5-02','CK1','A',300)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(0,'2018-5-09','CK2','A',500)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(0,'2018-5-10','CK3','A',200)
insert into Tx(bflag,ddate,ccode,cinvcode,qty)values(0,'2018-5-16','CK4','A',1000)

select case when bflag=1 then '入库' else '出库' end 标识,ddate as 单据日期,ccode as 单据号,cinvcode as 物料,qty as 数量
from tx order by ddate

IF OBJECT_ID('TEMPDB..#TX') IS NOT NULL
BEGIN
    DROP TABLE #TX
END

SELECT ROW_NUMBER()OVER(PARTITION BY CINVCODE,BFLAG ORDER BY CINVCODE,DDATE) AS IDX,BFLAG,DDATE,CCODE,CINVCODE,QTY,QTY AS iqty INTO #TX FROM TX WHERE BFLAG=1;

WITH cte as(
SELECT IDX,BFLAG,DDATE,ccode,CINVCODE,QTY,iqty FROM #TX WHERE IDX=1
union all
SELECT a.IDX,a.BFLAG,a.DDATE,a.ccode,a.CINVCODE,a.QTY,a.iqty+b.iqty as iqty FROM #TX a inner join cte b on a.IDX=b.idx+1 and a.cinvcode=b.cinvcode
)

select case when a.bflag=1 then '入库' else '出库' end as 标识,a.ddate as 单据日期,a.ccode as 单据号,a.cinvcode as 物料,
a.qty as 数量,a.iqty as 累计入,b.oqty as 累计出,a.iqty-ISNULL(b.oqty,0) as 差值,
case when a.iqty-ISNULL(b.oqty,0)>=qty then qty else a.iqty-ISNULL(b.oqty,0) end 单据余量,
case when a.iqty-ISNULL(b.oqty,0)>0 then 1 else 0 end as 标志
from cte a
left join (select cinvcode,sum(qty) as oqty from tx where bflag=0 group by cinvcode)b on a.cinvcode=b.cinvcode