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

SELECT

a.startDateTime,a.sessions

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

means that you should have:

WHERE (c.contactType IN (1, 3))

AND c.startdatetime >= @start

AND c.startdatetime < dateadd(day, 1, @end)

and not

WHERE (c.contactType IN (1, 3)) and c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime))

>WHERE a.number = @scheme

should be better in the subquery directly:

SELECT

a.startDateTime,a.sessions

, 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 >= @start

AND c.startdatetime < dateadd(day, 1,@end)

WHERE 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 = @scheme

) 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 >= @start

AND acd.startdatetime < dateadd(day, 1, @end )

GROUP BY sessionid, sessionseqnum

) b

ON a.sessionid = b.sessionID

AND a.sessionseqnum = b.sessionseqnum

however, this will probably not solve your query time.

to solve that, you will have to make some change.

to know what change this has to be, I will need to know how the

CASE WHEN c.calledNumber = xxx THEN yyy

has to work.

2 ideas

* either make that "translation/mapping" a computed field on the table ContactCallDetail, so you could index/query that computed field directly.

-> solution only to be used if that list of mappings is fixed (which I doubt)

* create a mapping table that translates the callednumber(s) into the values you need for that query, so you can include that table in your query.