根据每台机器每天的产能，自动编排机器生产日期、次序、数量，如下：
declare @t table(Machine char(1) ,d datetime,Minutes int,UsedMinute int)
insert into @t --两种设备A、B
select 'A','2009-06-29',600,100 union all --6月29號總時間600，已經佔用100
select 'A','2009-06-30',600,null union all
select 'A','2009-07-01',600,0 union all
select 'A','2009-07-02',400,null union all
select 'A','2009-07-03',600,null union all
select 'A','2009-07-04',700,null union all
select 'B','2009-06-29',600,null union all
select 'B','2009-06-30',600,0 union all
select 'B','2009-07-01',600,null union all
select 'B','2009-07-02',400,null union all
select 'B','2009-07-03',600,null
declare @v table(Item char(1),Machine char(1) ,d datetime,Minutes int)
insert into @v --物料K，需要用设备A、B.其中A从29号开始使用、B从01号开始使用
select 'K','A','2009-06-29',1500 union all
select 'M','A','2009-07-01',1000
如何写SQL或者用什么方法达到以下效果：
item Machine d UsedMinute
K A 2009-06-29 00:00:00.000 500
K A 2009-06-30 00:00:00.000 600
K A 2009-07-01 00:00:00.000 400
M A 2009-07-01 00:00:00.000 200
M A 2009-07-02 00:00:00.000 400
M A 2009-07-03 00:00:00.000 400
------解决方案--------------------
SELECT a.Item
, a.[Machine]
, b.d
, [数量] = CASE WHEN b.sum_B < a.sum_A THEN b.sum_B
ELSE a.sum_A
END - CASE WHEN b.sum_B - b.[Minutes] < a.sum_A - a.[Minutes] THEN a.sum_A - a.[Minutes]
ELSE b.sum_B - b.[Minutes]
END
FROM (
SELECT *, sum_A= (SELECT SUM ([Minutes]) FROM @v WHERE [Machine] = b.[Machine] AND d !> b.d) FROM @v b
) a
JOIN (
SELECT *
, sum_B = (
SELECT SUM ([Minutes] - ISNULL (UsedMinute, 0)) FROM @t WHERE [Machine] = a.[Machine] AND [d] !> a.[d]
)
FROM @t a
) b
ON a.[Machine] = b.[Machine]
AND b.sum_B - b.[Minutes] < a.sum_A
AND a.sum_A - a.[Minutes] < b.sum_B
/*
Item d Minutes Machine 数量
K 2009-06-29 00:00:00.000 600 A 500
K 2009-06-30 00:00:00.000 600 A 600
K 2009-07-01 00:00:00.000 600 A 400
M 2009-07-01 00:00:00.000 600 A 200
M 2009-07-02 00:00:00.000 400 A 400
M 2009-07-03 00:00:00.000 600 A 400
*/