Link to home
Start Free TrialLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Help needed with query

I have developed the following query below

SELECT
a.startDateTime,a.sessionseqnum,a.enddatetime,a.numbe,a.contactdisposition,a.customvariable2, a.connecttime, a.queuetime, b.talktime, b.worktime, b.holdtime
FROM        
(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
                       
FROM          
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?

Thanks
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

>@start and @end are datetime parameters
then, why do you put cast(@start as datetime) ?

anhdhow, what indexes do you have on your table?
can you upload the execution plan graph somewhere?
Avatar of Mark Wilson
Mark Wilson

ASKER

sorry the fields are datetime and the parameters are date

indexes are on c.calledNumber on the ContactCallDetail table

I will upload the execution plan later, as systems are down at the moment
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial