日期循环处理问题,可能需要用游标,请指点.谢谢!
有两个表:
t1:
      Date                 OT
2007-07-01             4
2007-07-10             6
2007-07-22             10

t2:
      Date               Leave
2007-07-03             2
2007-07-05             9
2007-07-31             10

问题,怎样用游标去进行两个表的记录相减.例如:
t2表的日期为最早的记录减去t1表的日期为最早的记录,如果有剩数就放到下一条去减.可能说得不清楚,希望大家能够理解.谢谢!这是我做加班和调休时遇到的问题.

------解决方案--------------------
上面的错了,改改

--建立测试环境
create table #t1(Date smalldatetime,加班 int)
insert #t1(Date,加班)
select '2007-07-01 ', '4 ' union all
select '2007-07-10 ', '6 ' union all
select '2007-07-22 ', '10 '
go
create table #t2(Date smalldatetime,请假 int)
insert #t2(Date,请假)
select '2007-07-03 ', '2 ' union all
select '2007-07-05 ', '9 ' union all
select '2007-07-05 ', '8 ' union all
select '2007-07-31 ', '10 '
go
--执行测试语句
declare @i int,@j int
select @i = sum(加班) from #t1
select @j = sum(请假) from #t2
if @i > @j
begin
update #t1
set @j = @j - 加班
,加班 = case when @j > =0 then 0 when @j < 0 and @j + 加班 > 0 then -@j else 加班 end
update #t2 set 请假 = 0
end
else if @i < @j
begin
update #t2
set @i = @i - 请假
,请假 = case when @i > =0 then 0 when @i < 0 and @i + 请假 > 0 then -@i else 请假 end
update #t1 set 加班 = 0
end
else
begin
update #t1 set 加班 = 0
update #t2 set 请假 = 0
end
go
select * from #t1
select * from #t2
--删除测试环境
drop table #t1,#t2
go
/*--测试结果
Date 加班
------------------------------------------------------ -----------
2007-07-01 00:00:00 0
2007-07-10 00:00:00 0
2007-07-22 00:00:00 0

(3 row(s) affected)

Date 请假
------------------------------------------------------ -----------
2007-07-03 00:00:00 0
2007-07-05 00:00:00 0
2007-07-05 00:00:00 0
2007-07-31 00:00:00 9

(4 row(s) affected)
*/

------解决方案--------------------
-- 用临时表计算结果, 应该比游标速度快
CREATE TABLE t1(Date DATETIME,OT INT)
INSERT T1
SELECT '2007-07-01 ', 4
UNION SELECT '2007-07-10 ', 6
UNION SELECT '2007-07-22 ', 10

CREATE TABLE t2(DatE DATETIME,Leave INT)
INSERT T2
SELECT '2007-07-03 ', 2
UNION SELECT '2007-07-05 ' , 9
UNION SELECT '2007-07-31 ' , 10


select id=identity(int,1,1),* into #1 from t1
select id=identity(int,1,1),* into #2 from t2


select a.id,c1=rtrim(a.ot)+ '+( '+rtrim((select isnull(sum(ot),0) from #1 where id <a.id)-(select isnull(sum(Leave),0) from #2 where id <b