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 */
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
*/