高手帮我解决这个对应问题，分不够可以加，在线等
租赁有关的，使用的单位是按月统计租金的，已有记录如下：
2007-05-05 上期结存 8 个
2007-05-06 购入 10 个
2007-05-07 调出 5 个
2007-05-08 调入 10 个
2007-05-09 调出 15 个
需要得到如下记录：（按照先进先出算）
2007-05-05 到 2007-05-10
起始日期 结束日期 天数 数量
2007-05-05 2007-05-07 2 5
2007-05-05 2007-05-09 4 3
2007-05-06 2007-05-09 3 10
2007-05-08 2007-05-09 1 2
2007-05-08 2007-05-10 2 8
急，高手帮忙呀
[解决办法]
后面的
2007-05-06 2007-05-09 3 10
2007-05-08 2007-05-09 1 2
2007-05-08 2007-05-10 2 8
数量又是什么来的?
[解决办法]
--测试环境
IF OBJECT_ID( 'tb ') IS NOT NULL
DROP TABLE tb
CREATE TABLE tb(
date datetime,
type nvarchar(10),
qty int)
INSERT tb(
date, type, qty)
SELECT '2007-05-05 ', N '上期结存 ', 8 UNION ALL
SELECT '2007-05-06 ', N '购入 ', 10 UNION ALL
SELECT '2007-05-07 ', N '调出 ', 5 UNION ALL
SELECT '2007-05-08 ', N '调入 ', 10 UNION ALL
SELECT '2007-05-09 ', N '调出 ', 15 UNION ALL
SELECT '2007-05-10 ', N '调出 ', 8
-- 查询
;WITH
OB AS(
SELECT
date, qty
FROM tb A
WHERE type = N '调出 '
),
O AS(
SELECT
date, qty,
qtySUM1 = ISNULL((SELECT SUM(qty) FROM OB WHERE date < A.date), 0),
qtySUM2 = ISNULL((SELECT SUM(qty) FROM OB WHERE date <= A.date), 0)
FROM OB A
),
IB AS(
SELECT
date, qty
FROM tb
WHERE type IN(N '上期结存 ', N '调入 ', N '购入 ')
),
I AS(
SELECT
date, qty,
qtySUM1 = ISNULL((SELECT SUM(qty) FROM IB WHERE date < A.date), 0),
qtySUM2 = ISNULL((SELECT SUM(qty) FROM IB WHERE date <= A.date), 0)
FROM IB A
),
RE AS(
SELECT
起始日期 = I.date ,
结束日期 = O.date,
天数 = DATEDIFF(Day, I.date, O.date),
I = CASE
WHEN I.qtySUM2 - O.qtySUM1 > I.qty THEN I.qty
ELSE I.qtySUM2 - O.qtySUM1
END,
O = CASE
WHEN O.qtySUM2 - I.qtySUM1 > O.qty THEN O.qty
ELSE O.qtySUM2 - I.qtySUM1
END
FROM I, O
WHERE O.date > = I.date
AND O.qtySUM1 < I.qtySUM2
AND O.qtySUM2 > I.qtySUM1
)
SELECT
起始日期, 结束日期, 天数,
数量 = CASE WHEN O > I THEN I ELSE O END
FROM RE
ORDER BY 起始日期, 结束日期
