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

求库存帐龄分析的SQL语句，求高手帮助
我有两个表，表一为库存表，表二为交易记录表（收货表，将数量调整成了负数）

我现在想要的结果是：通过这两个表，知道现有的库存量，是哪些库存交易对应的。
比如表1中10100 中的库存1000个，（1000-500-400-400）<=0(第一次小于等于0) 
得到：
itemid日期        Qty
101002013/6/20-500
101002013/5/23-400
101002013/4/20-100（原来这天是400，但只需要100）


表1中的 10157 中的库存量为：2000（2000-400-600-400-600） 
得到：
itemid日期        Qty
101572013/4/15-400
101572013/3/15-600
101572013/2/15-400
101572013/2/15-600


表一，和表二都是通过查询后得到如下的结果，记录很多，希望能在性能上做一些考虑。

表一：（4万条记录）
ITEMIDOn-hand
101001000
101572000



表二：（500万条记录）
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 帐龄分析
[解决办法]
估計沒有什麼神語句能做到高效查詢以上結果.

建議再做一表，存放收貨日期，收貨數量，減少時按先進先出的方法扣數.
[解决办法]

CREATE TABLE #items
(
itemid INT,
Onhand INT
)
INSERT #items(itemid, Onhand)
SELECT 10100, 1000 UNION ALL
SELECT 10157, 2000

CREATE 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

[解决办法]
--原始數據
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 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 
)

--開始運算
,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

查看更多 下一篇