请看一段Sql代码,要求按ID进行计算
我想按照ID号分别计算每个人的加班和请假,请运行下面代码,就明白我想怎样了.因为运行下面代码,如果不加ID的条件,就会按照日期一直计算下去,但加ID条件一次只能计算一个,请问怎样才能自动按照每个人去计算呢?谢谢!!!
create table #t1(ID int,Date smalldatetime,加班 decimal(18,2))
insert #t1(ID,Date,加班)
select '1 ', '2007-07-01 ', '5 ' union all
select '2 ', '2007-07-15 ', '5 '
go
create table #t2(ID int,Date smalldatetime,請假 decimal(18,2))
insert #t2(ID,Date,請假)
select '1 ', '2007-07-05 ', '10 ' union all
select '2 ', '2007-08-22 ', '10 '
go
declare @yearmonth varchar(50)
set @yearmonth= '2007-07 '
declare @i decimal(18,2),@j decimal(18,2),@id varchar(10)
select @i = isnull(sum(加班),0) from #t1 where convert(varchar(7),date,120)=@yearmonth
select @j = isnull(sum(請假),0) from #t2 where convert(varchar(7),date,120)=@yearmonth
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
where convert(varchar(7),date,120)=@yearmonth
update #t2 set 請假 = 0 where convert(varchar(7),date,120)=@yearmonth
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
where convert(varchar(7),date,120)=@yearmonth
update #t1 set 加班 = 0 where convert(varchar(7),date,120)=@yearmonth
end
else
begin
update #t1 set 加班 =0 where convert(varchar(7),date,120)=@yearmonth
update #t2 set 請假 = 0 where convert(varchar(7),date,120)=@yearmonth
end
go
select * from #t1
select * from #t2
go
drop table #t1,#t2
------解决方案----------------------看看是不是这样,用游标解决
create table #t1(ID int,Date smalldatetime,加班 decimal(18,2))
insert #t1(ID,Date,加班)
select '1 ', '2007-07-01 ', '5 ' union all
select '2 ', '2007-07-15 ', '5 '
go
create table #t2(ID int,Date smalldatetime,請假 decimal(18,2))
insert #t2(ID,Date,請假)
select '1 ', '2007-07-05 ', '