CREATE PROCEDURE recompair_tm AS
declare @startdate datetime
select @startdate= '07-1-1 '
create table #radlog_allgroup(acname varchar(50))
insert into #radlog_allgroup select distinct accountname from radlog_group where logondatetime> [email protected]
update tm_upload_all set flag1=1 from tm_upload_all,#radlog_allgroup where tm_upload_all.整理后的帐号=#radlog_allgroup.acname and tm_upload_all.开户日期> [email protected]
update tm_upload_all set flag2=1,fgad2=#radlog_allgroup.acname from tm_upload_all,#radlog_allgroup where #radlog_allgroup.acname like tm_upload_all.整理后的帐号+ '% ' and tm_upload_all.开户日期> [email protected]
update tm_upload_all set flag3=1,fgad3=#radlog_allgroup.acname from tm_upload_all,#radlog_allgroup where tm_upload_all.开户日期> [email protected] and #radlog_allgroup.acname like '% '+ tm_upload_all.整理后的帐号
GO
------解决方案--------------------
ALTER PROCEDURE recompair_tm AS
declare @startdate datetime
select @startdate= '07-1-1 '
create table #radlog_allgroup(acname varchar(50))
insert into #radlog_allgroup select distinct accountname from radlog_group where logondatetime> [email protected]
update tm_upload_all
set flag1=case when tm_upload_all.整理后的帐号=#radlog_allgroup.acname and tm_upload_all.开户日期> [email protected] then 1 else tm_upload_all.flag1 end,
flag2=case when #radlog_allgroup.acname like tm_upload_all.整理后的帐号+ '% ' and tm_upload_all.开户日期> [email protected] then 1 else tm_upload_all.flag2 end,
fgad2=case when #radlog_allgroup.acname like tm_upload_all.整理后的帐号+ '% ' and tm_upload_all.开户日期> [email protected] then #radlog_allgroup.acname else tm_upload_all.fgad2 end,
flag3=case when tm_upload_all.开户日期> [email protected] and #radlog_allgroup.acname like '% '+ tm_upload_all.整理后的帐号 then 1 else tm_upload_all.flag3 end,
fgad3=case when tm_upload_all.开户日期> [email protected] and #radlog_allgroup.acname like '% '+ tm_upload_all.整理后的帐号 then #radlog_allgroup.acname else tm_upload_all.fgad3 end
from tm_upload_all,#radlog_allgroup
GO
------解决方案--------------------
alter procedure recompair_tm AS
declare @startdate datetime
select @startdate= '07-1-1 '
update A
set A.flag1=1
from tm_upload_all A
where EXISTS(select * from radlog_group where logondatetime> [email protected] AND acname = A.整理后的帐号)
and A.开户日期 > = @startdate
update A
set A.flag2=1,
A.fgad2=B.acname
from tm_upload_all A,(select acname from radlog_group where logondatetime> [email protected] ) B
where B.acname like A.整理后的帐号+ '% '
and A.开户日期> [email protected]
update A
set A.flag3=1,
A.fgad3=B.acname
from tm_upload_all,(select acname from radlog_group where logondatetime> [email protected] ) B
where A.开户日期> [email protected]
and B.acname like '% '+ A.整理后的帐号
GO