DataPumpWindowsService/脚本/2022051202-UtilizationRateByHour.sql

USE [AECC]
GO
/****** Object:  StoredProcedure [dbo].[UtilizationRateByHour]    Script Date: 05/12/2022 10:49:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[UtilizationRateByHour](@searchdatechar varchar(24),@CityCode varchar(10)) as 
begin 

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

declare @logintime_hour00 int,
@logintime_hour01 int,
@logintime_hour02 int,
@logintime_hour03 int,
@logintime_hour04 int,
@logintime_hour05 int,
@logintime_hour06 int,
@logintime_hour07 int,
@logintime_hour08 int,
@logintime_hour09 int,
@logintime_hour10 int,
@logintime_hour11 int,
@logintime_hour12 int,
@logintime_hour13 int,
@logintime_hour14 int,
@logintime_hour15 int,
@logintime_hour16 int,
@logintime_hour17 int,
@logintime_hour18 int,
@logintime_hour19 int,
@logintime_hour20 int,
@logintime_hour21 int,
@logintime_hour22 int,
@logintime_hour23 int

declare @acwtime_hour00 int,
@acwtime_hour01 int,
@acwtime_hour02 int,
@acwtime_hour03 int,
@acwtime_hour04 int,
@acwtime_hour05 int,
@acwtime_hour06 int,
@acwtime_hour07 int,
@acwtime_hour08 int,
@acwtime_hour09 int,
@acwtime_hour10 int,
@acwtime_hour11 int,
@acwtime_hour12 int,
@acwtime_hour13 int,
@acwtime_hour14 int,
@acwtime_hour15 int,
@acwtime_hour16 int,
@acwtime_hour17 int,
@acwtime_hour18 int,
@acwtime_hour19 int,
@acwtime_hour20 int,
@acwtime_hour21 int,
@acwtime_hour22 int,
@acwtime_hour23 int

declare @acdtime_hour00 int,
@acdtime_hour01 int,
@acdtime_hour02 int,
@acdtime_hour03 int,
@acdtime_hour04 int,
@acdtime_hour05 int,
@acdtime_hour06 int,
@acdtime_hour07 int,
@acdtime_hour08 int,
@acdtime_hour09 int,
@acdtime_hour10 int,
@acdtime_hour11 int,
@acdtime_hour12 int,
@acdtime_hour13 int,
@acdtime_hour14 int,
@acdtime_hour15 int,
@acdtime_hour16 int,
@acdtime_hour17 int,
@acdtime_hour18 int,
@acdtime_hour19 int,
@acdtime_hour20 int,
@acdtime_hour21 int,
@acdtime_hour22 int,
@acdtime_hour23 int

declare @ihour int

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

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


set @begintime=convert(datetime,@searchdatechar)
set @endtime=DATEADD(day,1,@begintime)

--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)

set @ihour=1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

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

set @ihour=@ihour+1;

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

set @logintime_hour23=@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)

set @ihour=1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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


set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

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

set @ihour=@ihour+1;

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


select @i_acwtime2=isnull(sum(@ihour*3600),0) from #compute_agentstatelog
			where 
				begintime <= @begintime and (Endtime >= dateadd(hour,@ihour,@begintime) 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 < dateadd(hour,@ihour,@begintime)  


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

set @acwtime_hour23=@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
					  );

set @ihour=1;

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

set @ihour=@ihour+1;

select @acdtime_hour01=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour02=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour03=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour04=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour05=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour06=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour07=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour08=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour09=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour10=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour11=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour12=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour13=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour14=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour15=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour16=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour17=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour18=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour19=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour20=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour21=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour22=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

set @ihour=@ihour+1;

select @acdtime_hour23=
isnull(sum(talk_dur),0)
		from #t_stationcallrecord 
		where 1=1 and (End_Time >=@begintime and End_Time  <dateadd(hour,@ihour,@begintime))  
;

--select * into ##compute_agentloginlog from #compute_agentloginlog

--select @logintime_hour00 logintime_hour00,@acdtime_hour00 acdtime_hour00,@acwtime_hour00 acwtime_hour00

select case when @logintime_hour00=0 then 0 else round(cast( (@acdtime_hour00+@acwtime_hour00) as float)/@logintime_hour00,4) end as UtilizationRate00,
case when @logintime_hour01=0 then 0 else round(cast( (@acdtime_hour01+@acwtime_hour01) as float)/@logintime_hour01,4) end as UtilizationRate01,
case when @logintime_hour02=0 then 0 else round(cast( (@acdtime_hour02+@acwtime_hour02) as float)/@logintime_hour02,4) end as UtilizationRate02,
case when @logintime_hour03=0 then 0 else round(cast( (@acdtime_hour03+@acwtime_hour03) as float)/@logintime_hour03,4) end as UtilizationRate03,
case when @logintime_hour04=0 then 0 else round(cast( (@acdtime_hour04+@acwtime_hour04) as float)/@logintime_hour04,4) end as UtilizationRate04,
case when @logintime_hour05=0 then 0 else round(cast( (@acdtime_hour05+@acwtime_hour05) as float)/@logintime_hour05,4) end as UtilizationRate05,
case when @logintime_hour06=0 then 0 else round(cast( (@acdtime_hour06+@acwtime_hour06) as float)/@logintime_hour06,4) end as UtilizationRate06,
case when @logintime_hour07=0 then 0 else round(cast( (@acdtime_hour07+@acwtime_hour07) as float)/@logintime_hour07,4) end as UtilizationRate07,
case when @logintime_hour08=0 then 0 else round(cast( (@acdtime_hour08+@acwtime_hour08) as float)/@logintime_hour08,4) end as UtilizationRate08,
case when @logintime_hour09=0 then 0 else round(cast( (@acdtime_hour09+@acwtime_hour09) as float)/@logintime_hour09,4) end as UtilizationRate09,
case when @logintime_hour10=0 then 0 else round(cast( (@acdtime_hour10+@acwtime_hour10) as float)/@logintime_hour10,4) end as UtilizationRate10,
case when @logintime_hour11=0 then 0 else round(cast( (@acdtime_hour11+@acwtime_hour11) as float)/@logintime_hour11,4) end as UtilizationRate11,
case when @logintime_hour12=0 then 0 else round(cast( (@acdtime_hour12+@acwtime_hour12) as float)/@logintime_hour12,4) end as UtilizationRate12,
case when @logintime_hour13=0 then 0 else round(cast( (@acdtime_hour13+@acwtime_hour13) as float)/@logintime_hour13,4) end as UtilizationRate13,
case when @logintime_hour14=0 then 0 else round(cast( (@acdtime_hour14+@acwtime_hour14) as float)/@logintime_hour14,4) end as UtilizationRate14,
case when @logintime_hour15=0 then 0 else round(cast( (@acdtime_hour15+@acwtime_hour15) as float)/@logintime_hour15,4) end as UtilizationRate15,
case when @logintime_hour16=0 then 0 else round(cast( (@acdtime_hour16+@acwtime_hour16) as float)/@logintime_hour16,4) end as UtilizationRate16,
case when @logintime_hour17=0 then 0 else round(cast( (@acdtime_hour17+@acwtime_hour17) as float)/@logintime_hour17,4) end as UtilizationRate17,
case when @logintime_hour18=0 then 0 else round(cast( (@acdtime_hour18+@acwtime_hour18) as float)/@logintime_hour18,4) end as UtilizationRate18,
case when @logintime_hour19=0 then 0 else round(cast( (@acdtime_hour19+@acwtime_hour19) as float)/@logintime_hour19,4) end as UtilizationRate19,
case when @logintime_hour20=0 then 0 else round(cast( (@acdtime_hour20+@acwtime_hour20) as float)/@logintime_hour20,4) end as UtilizationRate20,
case when @logintime_hour21=0 then 0 else round(cast( (@acdtime_hour21+@acwtime_hour21) as float)/@logintime_hour21,4) end as UtilizationRate21,
case when @logintime_hour22=0 then 0 else round(cast( (@acdtime_hour22+@acwtime_hour22) as float)/@logintime_hour22,4) end as UtilizationRate22,
case when @logintime_hour23=0 then 0 else round(cast( (@acdtime_hour23+@acwtime_hour23) as float)/@logintime_hour23,4) end as UtilizationRate23


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