Declare @物品 varchar(20)
declare @最早進貨日期 DATETIME 
declare @目前庫存量  int
declare @本次進貨庫  int

declare cur_stock scroll cursor for
select 物品,存貨量
from table1 

open cur_stock
fetch next from cur_stock into @物品,@目前庫存量
while ( @@fetch_status<>-1 ) 
begin 
   declare cur_in scroll cursor for
   select 進貨日期,進貨量
   from table2
   where 物品=@物品
   order 進貨日期 desc
   
   open cur_in
   fetch next from cur_in into @最早進貨日期,@本次進貨庫
   while ( @@fetch_status<>-1 AND @目前庫存量 > 0 ) 
   begin
      select  @目前庫存量 = @目前庫存量 - @本次進貨庫 
      if @目前庫存量 > 0 
         fetch next from cur_in into @最早進貨日期,@本次進貨庫
      else 
         close cur_in
   end
   
   UPDATE table1 SET 最早進貨日期 =  @最早進貨日期 where 物品=@物品
   
   fetch next from cur_stock into @料號,@目前庫存量
end

若依照你的說法，ITEM A的最早進貨日應該為2016/11/27，而ITEM B的最早進貨日應該為2016/11/21，因為依照先進先出，只要最後進貨的數量加總>=你的現有庫存，那就是你的最早進貨日? 不是嗎?

昨晚接到海綿寶寶通知,來看這個案例.
我是使用 PostgreSQL, 以Window Functions 搭配CTE.
MS SQL Server 也是有同等的語法與相關函數,小地方可自行微調.
考慮到一般對Window Functions 應用較不熟悉,故將過程拆分,
一段一段展現過程與執行結果,邏輯判斷部分使用 case 方式,
符合ANSI SQL,不用特殊函數.最終會有兩種方式,兩種只是在
最後部分略有差異.
以下是Table建立,輸入資料以及推演過程之SQL Command.

create table ithelp170206a (
  item char(1) not null
, stock int not null  
);

insert into ithelp170206a values
('A', 300),
('B', 250);

create table ithelp170206b (
  item char(1) not null
, idate date not null
, qty int not null  
);

insert into ithelp170206b values
('A', '2016-10-11'::date, 70),
('A', '2016-11-13'::date, 200),
('A', '2016-11-27'::date, 250),
('A', '2016-12-30'::date, 50),
('B', '2016-11-11'::date, 30),
('B', '2016-11-21'::date, 100),
('B', '2016-12-23'::date, 210);

--
with revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
)
select *
  from revruntot;

 item |   idate    | qty | runt 
------+------------+-----+------
 A    | 2016-12-30 |  50 |   50
 A    | 2016-11-27 | 250 |  300
 A    | 2016-11-13 | 200 |  500
 A    | 2016-10-11 |  70 |  570
 B    | 2016-12-23 | 210 |  210
 B    | 2016-11-21 | 100 |  310
 B    | 2016-11-11 |  30 |  340
(7 筆資料列)

--
with revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
)
select r.*
     , a.stock
  from revruntot r
  join ithelp170206a a
 using (item)
 order by r.item, r.idate desc;

 item |   idate    | qty | runt | stock 
------+------------+-----+------+-------
 A    | 2016-12-30 |  50 |   50 |   300
 A    | 2016-11-27 | 250 |  300 |   300 runt >= stock start
 A    | 2016-11-13 | 200 |  500 |   300  <-- want
 A    | 2016-10-11 |  70 |  570 |   300
 B    | 2016-12-23 | 210 |  210 |   250
 B    | 2016-11-21 | 100 |  310 |   250  runt >= stock start
 B    | 2016-11-11 |  30 |  340 |   250  <-- want 
(7 筆資料列)
---

with 
revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
),
stat as (
select r.*
     , case  
       when (r.runt - a.stock) >= 0 then true
       else false
       end as stat
  from revruntot r
  join ithelp170206a a
 using (item)
)
select *
     , lag(stat) over(partition by item)
  from stat;

 item |   idate    | qty | runt | stat |  lag   
------+------------+-----+------+------+--------
 A    | 2016-12-30 |  50 |   50 | f    | [null]
 A    | 2016-11-27 | 250 |  300 | t    | f
 A    | 2016-11-13 | 200 |  500 | t    | t
 A    | 2016-10-11 |  70 |  570 | t    | t
 B    | 2016-12-23 | 210 |  210 | f    | [null]
 B    | 2016-11-21 | 100 |  310 | t    | f
 B    | 2016-11-11 |  30 |  340 | t    | t
(7 筆資料列)


---------------

with 
revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
),
stat as (
select r.*
     , case  
       when (r.runt - a.stock) >= 0 then true
       else false
       end as stat
  from revruntot r
  join ithelp170206a a
 using (item)
),
lagstat as (
select *
     , lag(stat) over(partition by item)
  from stat
)
select item
     , idate
  from lagstat
 where stat = True 
   and lag = True;

 item |   idate    
------+------------
 A    | 2016-11-13
 A    | 2016-10-11
 B    | 2016-11-11
(3 筆資料列)

--

with 
revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
),
stat as (
select r.*
     , case  
       when (r.runt - a.stock) >= 0 then true
       else false
       end as stat
  from revruntot r
  join ithelp170206a a
 using (item)
),
lagstat as (
select *
     , lag(stat) over(partition by item)
  from stat
),
samtru as (
select item
     , idate
  from lagstat
 where stat = True 
   and lag = True
)
select item
     , first_value(idate) over(partition by item order by idate desc)
  from samtru;

 item | first_value 
------+-------------
 A    | 2016-11-13
 A    | 2016-11-13
 B    | 2016-11-11
(3 筆資料列)

---

with 
revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
),
stat as (
select r.*
     , case  
       when (r.runt - a.stock) >= 0 then true
       else false
       end as stat
  from revruntot r
  join ithelp170206a a
 using (item)
),
lagstat as (
select *
     , lag(stat) over(partition by item)
  from stat
),
samtru as (
select item
     , idate
  from lagstat
 where stat = True 
   and lag = True
),
first_val as (
select item
     , first_value(idate) over(partition by item order by idate desc)
  from samtru
)
select item
     , first_value as earlydate
  from first_val
 group by item, first_value
 order by item;

 item | earlydate  
------+------------
 A    | 2016-11-13
 B    | 2016-11-11
(2 筆資料列)

時間: 0.852 ms
--
method 2
--
with 
revruntot as (
select *
     , sum(qty) over(partition by item order by idate desc) as runt
  from ithelp170206b
),
stat as (
select r.*
     , case  
       when (r.runt - a.stock) >= 0 then true
       else false
       end as stat
  from revruntot r
  join ithelp170206a a
 using (item)
),
lagstat as (
select *
     , lag(stat) over(partition by item)
  from stat
),
samtru as (
select item
     , idate
  from lagstat
 where stat = True 
   and lag = True
)
select item
     , max(idate) as earlydate
  from samtru
 group by item
 order by item;

 item | earlydate  
------+------------
 A    | 2016-11-13
 B    | 2016-11-11
(2 筆資料列)
 
時間: 0.690 ms

今天雖然寒流來襲，但看到以上邦友無私的熱血程式碼，
感覺IT邦真是很溫暖的地方啊!
ps.版主歹勢，偶寫不出這麼強的程式幫你~