下面是我在foxpro中写的代码,用来计算病假工资,现在我将表升迁到sql2000,要改在sql2000中用存储过程来实现,该怎么写呢??
*计算病假工资 bjgl 为计算病假工资的工龄,为实工龄,bjxs为病假工资系数
sele gzk
scan
bjgl=year(date())-1-year(gzny_xx)+int((month(date())+(13-month(gzny_xx)))/12)
do case
case bjgl<=2
bjxs=0.6
case bjgl>2 and bjgl<=4
bjxs=0.7
case bjgl>4 and bjgl<=6
bjxs=0.8
case bjgl>6 and bjgl<8
bjxs=0.9
case bjgl>=8
bjxs=1
endcase
repl bjgz_js with round(((jn_gz+gw_gz)/21*bjts_lr*bjxs),2)
endscan
程序中出现的 gzny_xx(日期型),jn_gz(数字型),gw_gz(数值型) 都是表中的字段,请高手帮忙!!!
*计算病假工资 bjgl 为计算病假工资的工龄,为实工龄,bjxs为病假工资系数
sele gzk
scan
bjgl=year(date())-1-year(gzny_xx)+int((month(date())+(13-month(gzny_xx)))/12)
do case
case bjgl<=2
bjxs=0.6
case bjgl>2 and bjgl<=4
bjxs=0.7
case bjgl>4 and bjgl<=6
bjxs=0.8
case bjgl>6 and bjgl<8
bjxs=0.9
case bjgl>=8
bjxs=1
endcase
repl bjgz_js with round(((jn_gz+gw_gz)/21*bjts_lr*bjxs),2)
endscan
程序中出现的 gzny_xx(日期型),jn_gz(数字型),gw_gz(数值型) 都是表中的字段,请高手帮忙!!!
select round(((jn_gz+gw_gz)/21*bjts_lr*(
case when bjgl <=2 then 0.6
when bjgl>2 and bjgl <=4 then 0.7
when bjgl>4 and bjgl <=6 then 0.8
when bjgl>6 and bjgl <8 then 0.9
when bjgl>=8 then 1 end),2)
from(
select jn_gz,gw_gz,bjts_lr
,bjgl= year(getdate())-1-year(gzny_xx)+(month(getdate())+(13-month(gzny_xx)))/12
from gzk)a
cast(((jn_gz+gw_gz)/21*bjts_lr*
case when year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) <= 2 then 0.6
when year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) > 2 and year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) <=4 then 0.7
when year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) > 4 and year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) <=6 then 0.8
when year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) > 6 and year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) < 8 then 0.9
when year(getdate()) - 1 - year(gzny_xx) + floor((month(getdate()) + (13 - month(gzny_xx)))/12) >=8 then 1.0
end
),decimal(18,2))
from tb
update gzk
set @t=year(getdate())-1-year(gzny_xx)+cast((month(getdate())+(13-month(gzny_xx)))/12 as int),
bjgz_js = round(((jn_gz+gw_gz)/21*bjts_lr*
case when @t<=2 then
0.6
when @t>2 and @t <=4 then
0.7
when @t>4 and @t <=6 then
0.8
when @t>6 and @t <8 then
0.9
when @t>=8 then
1
end
),2)
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
后面么?
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS
后面
as
begin
declare @t int
update gzk
set @t=year(getdate())-1-year(gzny_xx)+cast((month(getdate())+(13-month(gzny_xx)))/12 as int),
bjgz_js = round(((jn_gz+gw_gz)/21*bjts_lr*
case when @t<=2 then
0.6
when @t>2 and @t <=4 then
0.7
when @t>4 and @t <=6 then
0.8
when @t>6 and @t <8 then
0.9
when @t>=8 then
1
end
),2)
endgo
exec procname
as
begin
declare @t int
update gzk
set @t=year(getdate())-1-year(gzny_xx)+cast((month(getdate())+(13-month(gzny_xx)))/12 as int),
bjgz_js = round(((jn_gz+gw_gz)/21*bjts_lr*
case when @t<=2 then
0.6
when @t>2 and @t <=4 then
0.7
when @t>4 and @t <=6 then
0.8
when @t>6 and @t <8 then
0.9
when @t>=8 then
1
end
),2)
end
go
exec procname