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

表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 @tbselect '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 @tbAselect callerid,InboundCallTime,AgentId from @tb where AgentId=@Acc and InboundCallTime between @StartTime and @EndTimeorder 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 RepCntfrom @tbA A left join @tbA B on(A.id=B.id-1)group by A.AgentId,A.callerid

只适用小批量数据！