DataPumpWindowsService/脚本/2022051203-UtilizationRateByTime.sql

create proc UtilizationRateByTime(@searchdatechar varchar(24),@CityCode varchar(10)) as 
begin 

declare @begintime datetime
declare @endtime datetime
declare @orgId varchar(40)

declare @logintime_hour00 int

declare @acwtime_hour00 int

declare @acdtime_hour00 int

declare
@i_acwtime1 int,
@i_acwtime2 int,
@i_acwtime3 int,
@i_acwtime4 int

declare
@i_stafftime1 int,
@i_stafftime2 int,
@i_stafftime3 int,
@i_stafftime4 int

--开始时间，选择当天的00:00:00
set @begintime=convert(datetime,@searchdatechar)
--结束时间，下一天的00:00:00
set @endtime=DATEADD(day,1,@begintime)

--结束日期比当前时间大
if @endtime>=GETDATE()
set @endtime=getdate()


--set @begintime='2022-05-09'
--set @endtime='2022-05-10'

set @orgId='b05d5ac9-82b6-4abe-b343-36ddc94672d1'

if (@CityCode='SZ')
set @orgId='2b568031-39a1-4117-9927-39b7ade19e66'

if (@CityCode='JN')
set @orgId='f454f95c-9ab2-4499-936f-e746195c7487'


if OBJECT_ID('tempdb..#compute_agentloginlog') is not null
drop table #compute_agentloginlog
if OBJECT_ID('tempdb..#compute_agentstatelog') is not null
drop table #compute_agentstatelog
if OBJECT_ID('tempdb..#t_stationcallrecord') is not null
drop table #t_stationcallrecord
if OBJECT_ID('tempdb..#SearchAgent') is not null
drop table #SearchAgent

select distinct Agent.Agent into #SearchAgent from Agent with(nolock) join Org_Agent with(nolock) on 
Agent.AgentID=Org_Agent.AgentID join Organization with(nolock) on Org_Agent.OrgID=Organization.OrgID 
and Org_Agent.OrgID in (@orgId)


--某天每个小时的登录总时长

select * into #compute_agentloginlog from agentloginlog
		where 1=1
		and (
			logintime >= @begintime and logintime < @endtime  and logouttime >= @begintime and logouttime < @endtime 
			or (logintime <= @begintime and (logouttime >= @endtime or LogoutTime is null))
			or (logintime <= @begintime and logouttime >= @begintime and logouttime < @endtime)
			or (logintime >= @begintime and logintime < @endtime and (logouttime > @endtime or LogoutTime is null))
			)
			and agentid in (select agent from #SearchAgent)

--登入时间和登出时间都在@row_date_a和@row_date_b之间，此时计算logouttime-logintime=？
select @i_stafftime1=isnull(sum(isnull(datediff(ss,logintime, logouttime),0)),0)
	from #compute_agentloginlog
		where 
			logintime >= @begintime and logintime < @endtime  and logouttime >= @begintime and logouttime < @endtime 
			
--登入时间在@row_date_a之前，登出时间在@row_date_b之后或者没有登出时间，此时计算@intrvl*3600秒=？
select @i_stafftime2=isnull(sum(isnull(datediff(ss,@begintime, @endtime),0)),0)
	from #compute_agentloginlog
		where 
			logintime <= @begintime and (logouttime >= @endtime or LogoutTime is null)

--登入时间在@row_date_a之前，登出时间在@row_date_a和@row_date_b之间，此时计算logouttime-@row_date_a=？
select @i_stafftime3=isnull(sum(isnull(datediff(ss,@begintime, logouttime),0)),0)
		from #compute_agentloginlog
			where 
				logintime <= @begintime and logouttime >= @begintime and logouttime < @endtime
				 
--登入时间在@row_date_a和@row_date_b之间,登出时间在@row_date_b之后或者没有登出时间
select @i_stafftime4=isnull(sum(isnull(datediff(ss,logintime, @endtime),0)),0)
		from #compute_agentloginlog
			where 
				logintime >= @begintime and logintime < @endtime and (logouttime > @endtime or LogoutTime is null)

set @logintime_hour00=@i_stafftime1+@i_stafftime2+@i_stafftime3+@i_stafftime4


--每小时总的话后总时长

select * into #compute_agentstatelog from agentstatelog
		where 1=1 and state=2 
			and (
			(begintime >= @begintime and begintime < @endtime  and endtime >= @begintime and endtime < @endtime )
			or (begintime <= @begintime and (Endtime >= @endtime or Endtime is null))
			or (begintime < @begintime and endtime >= @begintime and endtime < @endtime)
			or (begintime >= @begintime and begintime < @endtime and (Endtime > @endtime or Endtime is null))
			)	  
			and agentid in (select Agent from #SearchAgent)

select @i_acwtime1=isnull(sum(isnull(datediff(ss,begintime, endtime),0)),0) from #compute_agentstatelog
		where 
			begintime >= @begintime and begintime < @endtime  and endtime >= @begintime and endtime < @endtime


select @i_acwtime2=isnull(sum(isnull(datediff(ss,@begintime, @endtime),0)),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= @endtime or Endtime is null)


select @i_acwtime3=isnull(sum(isnull(datediff(ss,@begintime, endtime),0)),0) from #compute_agentstatelog
			where 
				begintime < @begintime and endtime >= @begintime and endtime < @endtime  


select @i_acwtime4=isnull(sum(isnull(datediff(ss,begintime, @endtime),0)),0) from #compute_agentstatelog
			where 
				begintime >= @begintime and begintime < @endtime and (Endtime > @endtime or Endtime is null)    

set @acwtime_hour00=@i_acwtime1+@i_acwtime2+@i_acwtime3+@i_acwtime4


 --每小时总的通话总时长

 select * into #t_stationcallrecord
		from stationcallrecord with(nolock)
		where 1=1
				  and (End_Time >=@begintime and End_Time  <@endtime)  
				  --and split in ('841001','841002','841009','841012','841013','842030','841041','841301','841302','841303','841304','841305','841306','841307','841311','841312','841313','841314','841315','841316','841317','841341','841342','841343','841344','841345','841346','841347','841321','841322','841323','841324','841325','841326','841327','841331','841332','841333','841334','841335','841336','841337','841351','841352','841353','841354','841355','841356','841357')  
				      and calldirect=1 				      
				      and split != '' 
					  and AgentID in 
					  (
select Agent from #SearchAgent
					  );

select @acdtime_hour00=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <@endtime)  
;


select case when @logintime_hour00=0 then 0 else round(cast( (@acdtime_hour00+@acwtime_hour00) as float)/@logintime_hour00,4) end as UtilizationRate00


if OBJECT_ID('tempdb..#compute_agentloginlog') is not null
drop table #compute_agentloginlog

if OBJECT_ID('tempdb..#compute_agentstatelog') is not null
drop table #compute_agentstatelog

if OBJECT_ID('tempdb..#t_stationcallrecord') is not null
drop table #t_stationcallrecord

if OBJECT_ID('tempdb..#SearchAgent') is not null
drop table #SearchAgent

end