求SQL语句 ，倒推算最早入库时间
我有两个表，表一为库存表，表二为交易记录表（收货表，将数量调整成了负数）
我现在想要的结果是：通过这两个表，知道现有的库存量，最早一次收货是什么时候。
比如表1中10100 中的库存1000个，（1000-500-400-400）<=0(第一次小于等于0) 得出最早一次收货是2013-4-20
表1中的 10157 中的库存量为：2000（2000-400-600-400-600） 得出 2013/2/15
表一：
ITEMIDOn-hand
101001000
101572000
表二：
itemid日期 Qty
101002013/6/20-500
101002013/5/23-400
101002013/4/20-400
101002013/4/15-400
101572013/4/15-400
101572013/3/15-600
101572013/2/15-400
101572013/2/15-600
101572013/1/1-600 sql?语句 库存帐龄
[解决办法]
select a.ITEMID,a.[On-hand]
,max(b.日期) as 日期
from 表一 a,表二 b
where a.ITEMID = b.ITEMID
and a.[On-hand]+(select sum(Qty) from 表二 where ITEMID = a.ITEMID and 日期 >= b.日期)<= 0
group by a.ITEMID,a.[On-hand]
[解决办法]
with a1 (ITEMID,On_hand) as
(
select 10100,1000 union all
select 10157,2000
)
,a2 (itemid,date,Qty) as
(
select 10100,'2013/6/20',-500 union all
select 10100,'2013/5/23',-400 union all
select 10100,'2013/4/20',-400 union all
select 10100,'2013/4/15',-400 union all
select 10157,'2013/4/15',-400 union all
select 10157,'2013/3/15',-600 union all
select 10157,'2013/2/15',-400 union all
select 10157,'2013/2/15',-600 union all
select 10157,'2013/1/1',-600
)
,a3 as
(
select *,row_number() over (partition by itemid order by date desc) re from a2
)
,a4 as
(
select b.itemid,b.date,b.re,sum(a.qty) lj_qty
from a3 a
inner join a3 b on a.itemid=b.itemid and a.re<=b.re
group by b.itemid,b.date,b.re
)
,a5 as
(
select a.*,b.On_hand,a.lj_qty+b.On_hand cy
from a4 a
inner join a1 b on a.itemid=b.itemid
)
select a.itemid,a.On_hand,a.date
from a5 a
inner join (select itemid,min(re) re from a5 where cy<=0 group by itemid) b on a.itemid=b.itemid and a.re=b.re
order by a.itemid
