日期循环处理问题,可能需要用游标,请指点.谢谢!
有两个表:
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.id))+ ')- '+rtrim(b.leave),c1=a.ot+((select isnull(sum(ot),0) from #1 where id <a.id)-(select isnull(sum(Leave),0) from #2 where id <b.id))-b.leave
from #1 a inner join #2 b on a.id=b.id
drop table #1,#2
DROP TABLE T1,T2
/** 结果 ***
id c1 c1
----------- ---------------------------------------- -----------
1 4+(0)-2 2
2 6+(2)-9 -1
3 10+(-1)-10 -1
(3 row(s) affected)
**/
[解决办法]
declare @t_overtime table(date datetime,overtime int)
declare @t_leave table(date datetime,leave int)
declare @t table(o_date datetime,overtime int,l_date datetime,leave int,result int)
declare @v_o_date datetime,@v_l_date datetime,@v_overtime int,@v_leave int
declare @v_index int,@v_count int,@v_result int
set rowcount 10000000
insert into @t_overtime
select '2007-07-01 ',1
union all select '2007-07-02 ',3
union all select '2007-07-03 ',5
union all select '2007-07-04 ',2
union all select '2007-07-05 ',9
union all select '2007-07-06 ',1
union all select '2007-07-07 ',4
union all select '2007-07-08 ',8
union all select '2007-07-09 ',10
union all select '2007-07-10 ',6
union all select '2007-07-11 ',2
union all select '2007-07-12 ',3
union all select '2007-07-13 ',7
insert into @t_leave
select '2007-07-01 ',3
union all select '2007-07-03 ',1
union all select '2007-07-04 ',3
union all select '2007-07-05 ',4
union all select '2007-07-07 ',5
union all select '2007-07-08 ',2
union all select '2007-07-10 ',1
union all select '2007-07-11 ',6
union all select '2007-07-13 ',10
select @v_count=max(a.ct)from
(select count(*)as ct from @t_overtime
union all select count(*)as ct from @t_leave)a
set @v_index=1
set @v_o_date= '1900-01-01 '
set @v_l_date= '1900-01-01 '
set @v_result=0
while @v_count> =@v_index
begin
set rowcount @v_index
if exists(select 1 from @t_overtime where date> @v_o_date)
select top 1 @v_o_date=date,@v_overtime=overtime from @t_overtime where date> @v_o_date order by date
else
select @v_o_date=null,@v_overtime=null
if exists(select 1 from @t_leave where date> @v_l_date)
select top 1 @v_l_date=date,@v_leave=leave from @t_leave where date> @v_l_date order by date
else
select @v_l_date=null,@v_leave=null
set @v_result=isnull(@v_result,0)+isnull(@v_overtime,0)-isnull(@v_leave,0)
insert into @t
select @v_o_date,@v_overtime,@v_l_date,@v_leave,@v_result
set @v_index=@v_index+1
end
select *from @t
/*结果
o_date overtime l_date leave result
----------------------- ----------- ----------------------- ----------- -----------
2007-07-01 00:00:00.000 1 2007-07-01 00:00:00.000 3 -2
2007-07-02 00:00:00.000 3 2007-07-03 00:00:00.000 1 0
2007-07-03 00:00:00.000 5 2007-07-04 00:00:00.000 3 2
2007-07-04 00:00:00.000 2 2007-07-05 00:00:00.000 4 0
2007-07-05 00:00:00.000 9 2007-07-07 00:00:00.000 5 4
2007-07-06 00:00:00.000 1 2007-07-08 00:00:00.000 2 3
2007-07-07 00:00:00.000 4 2007-07-10 00:00:00.000 1 6
2007-07-08 00:00:00.000 8 2007-07-11 00:00:00.000 6 8
2007-07-09 00:00:00.000 10 2007-07-13 00:00:00.000 10 8
2007-07-10 00:00:00.000 6 NULL NULL 14
2007-07-11 00:00:00.000 2 NULL NULL 16
2007-07-12 00:00:00.000 3 NULL NULL 19
2007-07-13 00:00:00.000 7 NULL NULL 26
*/
[解决办法]
--我想如果楼主想查看的是一段时间的加班和请假状况，下面的应该更适合吧
declare @t_overtime table(date datetime,overtime int)
declare @t_leave table(date datetime,leave int)
declare @t table(date datetime,overtime int,leave int,result int)
declare @v_overtime int,@v_leave int,@v_result int
declare @v_StartDate datetime---开始时间
declare @v_EndDate datetime---结束时间
insert into @t_overtime
select '2007-07-01 ',1
union all select '2007-07-02 ',3
union all select '2007-07-03 ',5
union all select '2007-07-04 ',2
union all select '2007-07-05 ',9
union all select '2007-07-06 ',1
union all select '2007-07-07 ',4
union all select '2007-07-08 ',8
union all select '2007-07-09 ',10
union all select '2007-07-10 ',6
union all select '2007-07-11 ',2
union all select '2007-07-12 ',3
union all select '2007-07-13 ',7
insert into @t_leave
select '2007-07-01 ',3
union all select '2007-07-03 ',1
union all select '2007-07-04 ',3
union all select '2007-07-05 ',4
union all select '2007-07-07 ',5
union all select '2007-07-08 ',2
union all select '2007-07-10 ',1
union all select '2007-07-11 ',6
union all select '2007-07-13 ',10
set @v_StartDate= '2007-07-01 '
set @v_EndDate= '2007-07-31 '
set @v_result=0
while @v_StartDate <=@v_EndDate
begin
select @v_overtime=overtime from @t_overtime where date=@v_StartDate
select @v_leave=leave from @t_leave where date=@v_StartDate
if not exists(select 1 from @t_overtime where date=@v_StartDate)set @v_overtime=0
if not exists(select 1 from @t_leave where date=@v_StartDate)set @v_leave=0
set @v_result=isnull(@v_result,0)+isnull(@v_overtime,0)-isnull(@v_leave,0)
insert into @t
select @v_StartDate,isnull(@v_overtime,0),isnull(@v_leave,0),@v_result
set @v_StartDate=dateadd(day,1,@v_StartDate)
end
select *from @t
