I have developed the following query below
a.startDateTime,a.sessionseqnum,a.enddatetime,a.numbe,a.contactdisposition,a.customvariable2, a.connecttime, a.queuetime, b.talktime, b.worktime, b.holdtime
(SELECT c.sessionid, c.startDateTime, c.sessionseqnum, c.enddatetime,
CASE WHEN c.calledNumber = '11527' THEN '11574' WHEN c.calledNumber IN (11577', '11578') THEN '11567' WHEN c.calledNumber = '11566' THEN '11562' WHEN c.calledNumber = '11586' THEN '11589' WHEN c.calledNumber IN ('11565',
'11571', '11548', '11505', '11769') THEN '11534' WHEN c.calledNumber = '11504' THEN '11608' WHEN c.calledNumber = '11575' THEN '11609' ELSE c.calledNumber END AS number, c.contactdisposition, case when c.customVariable1 ='' then c.customvariable2 else customvariable1 end customvariable2, c.connecttime, crd.queuetime
ContactCallDetail c LEFT OUTER JOIN ContactRoutingDetail crd ON c.sessionSeqNum = crd.sessionSeqNum AND c.sessionID = crd.sessionID
WHERE (c.contactType IN (1, 3)) and c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime)) a LEFT OUTER JOIN
(SELECT sessionID, sessionseqnum, SUM(talktime) AS talktime, SUM(holdtime) AS holdtime, SUM(worktime) AS worktime
FROM agentconnectiondetail acd
where acd.startdatetime >= cast(@start as datetime) AND acd.startdatetime < cast(@end +1 as datetime)
GROUP BY sessionid, sessionseqnum) b ON a.sessionid = b.sessionID AND a.sessionseqnum = b.sessionseqnum
WHERE a.number = @scheme
@start and @end are datetime parameters converted to date only and @scheme is an int.
The problem I have is that the date query is slow, there is not a huge amount of data being returned but the query chugs. The problem from the execution plan is around the aggregations and the WHERE a.number = @scheme.
Is there a better way to write this?