求库存帐龄分析的SQL语句，求高手帮助

Question

我有两个表，表一为库存表，表二为交易记录表（收货表，将数量调整成了负数）我现在想要的结果是：通过这两个表，知道现有的库存量，是哪些库存交易对应的。
比如表1中10100 中的库存1000个，（1000-500-400-400）<=0(第一次小于等于0) 
得到：
itemid 日期         Qty
10100 2013/6/20 -500
10100 2013/5/23 -400
10100 2013/4/20 -100（原来这天是400，但只需要100）
表1中的 10157 中的库存量为：2000（2000-400-600-400-600） 
得到：
itemid 日期         Qty
10157 2013/4/15 -400
10157 2013/3/15 -600
10157 2013/2/15 -400
10157 2013/2/15 -600
表一，和表二都是通过查询后得到如下的结果，记录很多，希望能在性能上做一些考虑。表一：（4万条记录）
ITEMID On-hand
10100 1000
10157 2000表二：（500万条记录）
itemid 日期         Qty
10100 2013/6/20 -500
10100 2013/5/23 -400
10100 2013/4/20 -400
10100 2013/4/15 -40010157 2013/4/15 -400
10157 2013/3/15 -600
10157 2013/2/15 -400
10157 2013/2/15 -600
10157 2013/1/1 -600SQL帐龄分析

Answer 1

CREATE TABLE #items
(
itemid INT,
Onhand INT
)
INSERT #items(itemid, Onhand)
SELECT 10100, 1000 UNION ALL
SELECT 10157, 2000CREATE TABLE #itemrecord
(
sysid INT IDENTITY NOT NULL,
itemid INT,
thedate DATETIME,
qty INT
)
INSERT #itemrecord(itemid, thedate, qty)
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--SQL
--#items表:itemid,聚集索引
--#itemrecord表:itemid, sysid, qty 非聚集索引--仅供参考 
SELECT
k.sysid,
k.itemid,
k.thedate,
qty = (CASE WHEN n.sysid = k.sysid THEN n.total - m.Onhand + k.qty ELSE k.qty end)
FROM #items m
CROSS APPLY
(
SELECT TOP(1) z.sysid, z.total FROM
(
SELECT
sysid,      
itemid, 
total = (SELECT ABS(SUM(qty)) FROM #itemrecord b WHERE b.itemid = a.itemid AND b.sysid <= a.sysid)
FROM #itemrecord a
) z
WHERE z.itemid = m.itemid
AND z.total >= m.Onhand
ORDER BY z.total
) n
INNER JOIN #itemrecord k
ON m.itemid = k.itemid
AND n.sysid >= k.sysid

Answer 2

非单纯SQL语句问题，而是设计问题

Answer 3

--原始數據
with a01 (itemid,[On-hand]) as
(
select 10100,1000 union all
select 10157,2000
)
,a02 (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 allselect 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 
)--開始運算
,a1 as
(
select date,itemid,sum(qty) qty
from a02
group by date,itemid
)
,a2 as
(
select itemid,sum([On-hand]) [On-hand]
from a01
group by itemid
)
,a3 as
(
SELECT aa.date,aa.itemid,aa.qty, SUM(lj.qty) AS lj_qty
FROM a1 lj 
INNER JOIN a1 aa ON lj.itemid=aa.itemid and lj.date >= aa.date
GROUP BY aa.date,aa.itemid,aa.qty
)
,a4 as
(
select a.*,b.[On-hand],a.lj_qty+b.[On-hand] cy,
row_number() over(partition by a.itemid order by a.date desc) re
from a3 a
inner join a2 b on a.itemid=b.itemid
)
,a5 as
(
select itemid,min(re) re from a4 where cy<=0 group by itemid
)
select a.itemid,a.date,case when a.cy>=0 then a.qty else a.qty-a.cy end qty
from a4 a
inner join a5 b on a.itemid=b.itemid and a.re<=b.re
ORDER BY a.itemid,a.date desc