create procedure deletemibowebtime(@id varchar(50))
as
declare @total_time float,
@changetime float,
@rate float,
@webaddress varchar(200),
@total_timemax float
declare web_cur cursor for
select web_add,total_time from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0) and (datediff(year, LOGIN_DATE,GETDATE())=0)
and total_time>=3*60*1000
open web_cur
Fetch next from web_cur into @webaddress,@total_timemax
print @webaddress
while @@Fetch_status=0
begin
select @changetime=sum(total_time) from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0) and (datediff(year, LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')
select @total_time=sum(total_time) from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0) and (datediff(year, LOGIN_DATE,GETDATE())=0)
set @rate= (@total_time-@changetime)/@total_time
if @rate>0
begin
update OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
).WEB_Log.dbo.WB_HOURS
set H_00=H_00*@rate,H_01=H_01*@rate,
H_02=H_02*@rate,H_03=H_03*@rate,H_04=H_04*@rate,
H_05=H_05*@rate,H_06=H_06*@rate,H_07=H_07*@rate,
H_08=H_08*@rate,H_09=H_09*@rate,H_10=H_10*@rate,
H_11=H_11*@rate,H_12=H_12*@rate,H_13=H_13*@rate,
H_14=H_14*@rate,H_15=H_15*@rate,H_16=H_16*@rate,
H_17=H_17*@rate,H_18=H_18*@rate,H_19=H_19*@rate,
H_20=H_20*@rate,H_21=H_21*@rate,H_22=H_22*@rate,
H_23=H_23*@rate, total_time = total_time*@rate
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0)
and (datediff(year, LOGIN_DATE,GETDATE())=0)
delete from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=10.112.65.6;User ID=LogDel;Password=LogDel'
).WEB_Log.dbo.WEB_DAY_ADDRESS
WHERE (network_acc ='UNIMICRON/'+@id+'')
AND (datediff(month, LOGIN_DATE,GETDATE())=0) and (datediff(year, LOGIN_DATE,GETDATE())=0)
and web_add in (''+@webaddress+'')
end
Fetch next from web_cur into @webaddress,@total_timemax
end
close web_cur
deallocate web_cur
GO