统计某个接线员接打电话记录

表	CallLog


电话号码	callerid
来电时间	InboundCallTime
接电话人	AgentId


参数一：开始时间、StartTime
参数二：结束时间、EndTime
参数三：接电话人、Account_ID


要求:统计出接电话人(Account_ID)在开始时间(StartTime)和结束时间(EndTime)内接电话的数量，另外在这个指定时间内，5天内有相同的电话号码只统计一次。

declare @tb table
(
 callerid nvarchar(30),
 InboundCallTime DateTime,
 AgentId nvarchar(30)
)

insert into @tb
select '1802221010','2011-10-10','001' union all 
select '1802221010','2011-10-11','001' union all 
select '1802221010','2011-10-14','001' union all 
select '1802221010','2011-10-29','001' union all 
select '1802221011','2011-10-10','001' union all 
select '1802221012','2011-10-11','001' union all 
select '1802221011','2011-10-14','001' union all 
select '1802221013','2011-10-29','001' union all 
select '18058692','2011-10-10','002' union all 
select '220-1522015','2011-10-11','003' union all 
select '13965568974','2011-10-24','002' union all 
select '13965568974','2011-10-29','004'  

--接电话人 
declare @Acc nvarchar(30) set @Acc = '001'
--开始时间 
declare @StartTime DateTime set @StartTime = '2011-05-01'
--结束时间 
declare @EndTime DateTime set @EndTime = '2011-10-30'
--临时表。使用递增编号
declare @tbA table
(
  id int identity(1,1),
  callerid nvarchar(30),
  InboundCallTime DateTime,
  AgentId nvarchar(30)
)
--根据输入参数压入临时表数据
insert into @tbA
select callerid,InboundCallTime,AgentId from @tb 
where AgentId=@Acc and InboundCallTime between @StartTime and @EndTime
order by callerid,InboundCallTime
--检索每个电话接打次数
select 
A.callerid,A.AgentId,
sum
(
   case 
        when B.id is null then 1 
        when (abs(datediff(day,A.InboundCallTime,B.InboundCallTime))>5) and (A.callerid=B.callerid) then 1
        when B.callerid<>A.callerid then 1
        else 0
   end
) as RepCnt
from @tbA A left join @tbA B on(A.id=B.id-1)
group by A.AgentId,A.callerid

只适用小批量数据！