declare @qxts1 integer,@tsc integer,@qxts2 integer,@jsje decimal(18,2),@kl Numeric(18,3)
declare @rq datetime , @fhrq datetime,@mylx Numeric(18,3),@hlts integer,@fhje decimal
set @jsje=0
set @qxts2=0
set @kl=0
--set @qjc=O
set @tsc=0 --期限天数差
set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
set @fhje=1000
if not exists(select * from kxszh where qxts>@hlts and xsbm='001') return
declare C_kxxh cursor for select qxts,mylx from kxszh where xsbm='001' order by qxts
open C_kxxh
fetch next from C_kxxh into @qxts1,@mylx
while @@fetch_status=0 begin
set @tsc=@qxts1-@qxts2
select @tsc as tsc ,@qxts1 as qxts1,@qxts2 as qxts2,@mylx as mylx
if @hlts>@tsc
set @jsje=@jsje+@tsc*@kl*1/(100*30)* @fhje
else
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
set @hlts=@hlts-@tsc
if @hlts <=0 break
set @qxts2=@qxts1
set @kl=@mylx
fetch next from C_kxxh into @qxts1,@mylx
end
if @hlts>0
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
close C_kxxh
deallocate C_kxxh SELECT jsje=@jsje,qxts1=@qxts1,mylx=@mylx,hlts=@hlts,tsc=@tsc
select qxts,mylx from kxszh where xsbm='001' order by qxts
不过现在有个问题就是:如果我的HLTS>QXTS(max)
就会出错 create table kxszh(qxts int,mylx decimal(10,1),xsbm int)
insert into tb select 10,0.5,001
insert into tb select 20,0.6,001
insert into tb select 30,0.7,001
set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
如果HLTS>30
就会出错
declare @rq datetime , @fhrq datetime,@mylx Numeric(18,3),@hlts integer,@fhje decimal
set @jsje=0
set @qxts2=0
set @kl=0
--set @qjc=O
set @tsc=0 --期限天数差
set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
set @fhje=1000
if not exists(select * from kxszh where qxts>@hlts and xsbm='001') return
declare C_kxxh cursor for select qxts,mylx from kxszh where xsbm='001' order by qxts
open C_kxxh
fetch next from C_kxxh into @qxts1,@mylx
while @@fetch_status=0 begin
set @tsc=@qxts1-@qxts2
select @tsc as tsc ,@qxts1 as qxts1,@qxts2 as qxts2,@mylx as mylx
if @hlts>@tsc
set @jsje=@jsje+@tsc*@kl*1/(100*30)* @fhje
else
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
set @hlts=@hlts-@tsc
if @hlts <=0 break
set @qxts2=@qxts1
set @kl=@mylx
fetch next from C_kxxh into @qxts1,@mylx
end
if @hlts>0
set @jsje=@jsje+@hlts*@kl*1/(100*30)* @fhje
close C_kxxh
deallocate C_kxxh SELECT jsje=@jsje,qxts1=@qxts1,mylx=@mylx,hlts=@hlts,tsc=@tsc
select qxts,mylx from kxszh where xsbm='001' order by qxts
不过现在有个问题就是:如果我的HLTS>QXTS(max)
就会出错 create table kxszh(qxts int,mylx decimal(10,1),xsbm int)
insert into tb select 10,0.5,001
insert into tb select 20,0.6,001
insert into tb select 30,0.7,001
set @fhrq='2008-03-01'
set @rq='2008-03-12'
set @hlts=datediff(day,@fhrq,@rq)
如果HLTS>30
就会出错
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货