请看一段测试代码,寻求解决方法.谢谢!
代码:
Create Table #t1(EmployeeID int,WorkOnDate datetime,AskForLeaveType varchar(50),OTType varchar(50),加班 int,status int)
insert #t1(EmployeeID,WorkOnDate,AskForLeaveType,OTType,加班,status)
select 1, '2007-07-06 ', '加班 ', 'A ', '3 ',0 union all
select 1, '2007-07-12 ', '加班 ', 'A ', '4 ',0 union all
select 1, '2007-07-15 ', '加班 ', 'B ', '8 ',0 union all
select 1, '2007-07-18 ', '加班 ', 'A ', '3 ',0
go
Create Table #t2(EmployeeID int,WorkOnDate datetime,AskForLeaveType varchar(50),請假 int,status int)
insert #t2(EmployeeID,WorkOnDate,AskForLeaveType,請假,status)
select 1, '2007-07-21 ', '請假 ', '4 ',0 union all
select 1, '2007-07-27 ', '請假 ', '8 ',0
go
declare @StartDate varchar(10),@EndDate varchar(10)
set @StartDate= '2007-07-01 '
set @EndDate= '2007-07-31 '
declare @EmployeeID int
declare id_cursor cursor for
select EmployeeID from #t1 union select EmployeeID from #t2
open id_cursor
fetch next from id_cursor into @EmployeeID
while @@fetch_Status = 0
begin
declare @i decimal(18,2),@j decimal(18,2)
select @i = isnull(sum(加班),0) from #t1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
select @j = isnull(sum(請假),0) from #t2 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
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,Status=1
where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
update #t2 set 請假 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
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,Status=1
where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
update #t1 set 加班 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
end
else
begin
update #t1 set 加班 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
update #t2 set 請假 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
end
fetch next from id_cursor into @EmployeeID
end
close id_cursor
deallocate id_cursor
select * from #t1
select * from #t2
go
drop table #t1,#t2
--------------------------------------
问题:在#t1表里面有一个OtType字段,要求先计算完B类再计算A类.谢谢!
PS:请先运行一次,各位就应该明白我想怎样了.谢谢!
[解决办法]
Create Table #t1(EmployeeID int,WorkOnDate datetime,AskForLeaveType varchar(50),OTType varchar(50),加班 int,status int)
insert #t1(EmployeeID,WorkOnDate,AskForLeaveType,OTType,加班,status)
select 1, '2007-07-06 ', '加班 ', 'A ', '3 ',0 union all
select 1, '2007-07-12 ', '加班 ', 'A ', '4 ',0 union all
select 1, '2007-07-15 ', '加班 ', 'B ', '8 ',0 union all
select 1, '2007-07-18 ', '加班 ', 'A ', '3 ',0
go
Create Table #t2(EmployeeID int,WorkOnDate datetime,AskForLeaveType varchar(50),請假 int,status int)
insert #t2(EmployeeID,WorkOnDate,AskForLeaveType,請假,status)
select 1, '2007-07-21 ', '請假 ', '4 ',0 union all
select 1, '2007-07-27 ', '請假 ', '8 ',0
go
declare @StartDate varchar(10),@EndDate varchar(10)
set @StartDate= '2007-07-01 '
set @EndDate= '2007-07-31 '
declare @EmployeeID int
declare id_cursor cursor for
select EmployeeID from #t1 union select EmployeeID from #t2
open id_cursor
fetch next from id_cursor into @EmployeeID
while @@fetch_Status = 0
begin
declare @i decimal(18,2),@j decimal(18,2)
select @i = isnull(sum(加班),0) from #t1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
select @j = isnull(sum(請假),0) from #t2 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
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,Status=1
where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
--加个条件
and OTType = 'B '
--加这句
update #t1
set @j = @j - 加班
,加班 = case when @j > =0 then 0 when @j < 0 and @j + 加班 > 0 then -@j else 加班 end,Status=1
where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
and OTType = 'A '
update #t2 set 請假 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
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,Status=1
where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
update #t1 set 加班 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
end
else
begin
update #t1 set 加班 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
update #t2 set 請假 = 0,Status=1 where WorkOnDate> =@StartDate and WorkOnDate <=@EndDate and EmployeeID = @EmployeeID
end
fetch next from id_cursor into @EmployeeID
end
close id_cursor
deallocate id_cursor
select * from #t1
select * from #t2
go
drop table #t1,#t2
[解决办法]
最笨的办法，将你那段代码运行两次，第一次计算A类，第二次计算B类，只要在相应的条件里加个and OTType = 'A '和 and OTType = 'B '即可
