DECLARE @dateFrom DATETIME, @dateTo DATETIME, @SLLimit INT SET @dateFrom = '2014-11-12 00:00:00' SET @dateTo = '2014-11-13 00:00:00' SET @SLLimit = 20 SELECT SUM(1) AS AllCalls, SUM(CASE WHEN tcd.NetQTime <= @SLLimit THEN 1 ELSE 0 END) AS SLMet FROM Termination_Call_Detail tcd WHERE tcd.DateTime >= @dateFrom AND tcd.DateTime < @dateTo AND tcd.AgentSkillTargetID IS NOT NULL AND tcd.SkillGroupSkillTargetID IS NOT NULL AND tcd.RouterCallKeyDay > 0 AND tcd.TalkTime > 0
The result of this query is actually two numbers: AllCalls gives you the number of calls (explained bellow) and the SLMet is the number of calls where the length of time spent in the Router Queue (NetQTime) is less than or equal to 20 seconds.
The input parameters are
@dateFrom, @dateTo: the start and the end of the observed period;
@SLLimit: the upper bound of the interval for one call's queue time, if it is not passed then the call is considered answered within this "service level" interval.
Please note there are multiple conditions in the WHERE clause: the AgentSkillTargetID and the SkillGroupSkillTargetID cannot be NULL (this ensures we only take a look at calls handled by agents and skill groups) also the RouterCallKeyDay > 0 ensures we count ACD calls only and the TalkTime > 0 filters out answered calls only.
You can sort of enhance this with for instance
SUM(CASE WHEN (tcd.NetQTime + tcd.RingTime) <= @SLLimit THEN 1 ELSE 0 END) AS SLMet