Longest Wait to Abandon

[roberteliasson]

Hi all,

I´m trying to find out the longest wait to abandon in the Call_Type_Interval table.

However, it seem to sum together the "DelayQAbandTime" on an interval instead of picking the MAX DelayQAbandTime.

This makes the "Longest wait to abandon" being too high, if I compare it to TimeToAband in Termination_Call_Detail.

Any ideas on how I can gather this figure without using the TCD table?

Thanks!

[roberteliasson]

Here is the current query, where I´m picking MAX(TimeToAband) from TCD2.

This report takes AGES to run - any tips?

SELECT

CT.EnterpriseName,

TCD.CallTypeID,

(CASE WHEN SUM(TCD.Coded * 1.0) = 0 THEN 0 ELSE SUM(TCD.Negative * 1.0)/SUM(TCD.Coded * 1.0) END) as FailureDemand,

SUM(TCD.Negative) as Negative,

SUM(TCD.Coded) as Contacts,

sum(isnull(CTI.AnswerWaitTime,0)) as AnswerWaitTime,

sum(isnull(CTI.CallsOffered,0)) as CallsOffered, 

sum(isnull(CTI.CallsAnswered , 0))  as CallsAnswered,

sum(isnull(CTI.CallsAnswered , 0) * 1.0)/sum(isnull(CTI.CallsOffered,0) * 1.0)  as perAnswered,

MAX(isnull(TCD2.max_aban_delay,0)) as max_aban_delay,

SUM(isnull(CTI.DelayQAbandTime,0))/SUM(isnull(CTI.TotalCallsAband,0)) as avg_aban_delay,

SUM(isnull(CTI.DelayQAbandTime,0)) as CallDelayAbandTime,

SUM(isnull(CTI.TotalCallsAband,0)) as TotalCallsAband

FROM

(SELECT CallTypeID,

EnterpriseName

FROM Call_Type

) CT

LEFT OUTER JOIN (SELECT

DateTime,

CallTypeID,

Negative = Count(CASE WHEN (WrapupData LIKE 'i 04%' OR WrapupData LIKE 'i 05%' OR WrapupData LIKE 'i 06%' OR WrapupData LIKE 'i 07%') THEN WrapupData END),

Coded = Count(WrapupData)

FROM Termination_Call_Detail

WHERE WrapupData LIKE 'i %'

AND WrapupData is not null

AND DateTime >= :startDate

AND DateTime <= :endDate

AND CallTypeID IN (:CallTypeID)

GROUP BY

CallTypeID,

DateTime

) TCD

ON TCD.CallTypeID=CT.CallTypeID

LEFT OUTER JOIN (SELECT

CallTypeID,

max_aban_delay = MAX(TimeToAband)

FROM Termination_Call_Detail

WHERE DateTime >= :startDate

AND DateTime <= :endDate

AND CallTypeID IN (:CallTypeID)

and TimeToAband is not null

GROUP BY

CallTypeID,

DateTime

) TCD2

ON TCD2.CallTypeID=CT.CallTypeID

LEFT OUTER JOIN (SELECT

CallTypeID,

DateTime,

AnswerWaitTime,  

CallsOffered,   

CallsAnswered,

TotalCallsAband,  

CallDelayAbandTime,

DelayQAbandTime

FROM Call_Type_Interval 

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

) CTI

ON CTI.CallTypeID = TCD.CallTypeID

WHERE TCD.DateTime >= :startDate

AND TCD.DateTime <= :endDate

AND CT.CallTypeID IN (:CallTypeID)

GROUP BY CT.EnterpriseName,

TCD.CallTypeID

[jacparke]

You're going at the TCD table twice, and OUTERS are expensive.  Also, while you have the CallTypeID, you're not using it at every invocation.  Finally, SQLServer seems to like Virtual tables a whole lot more than this sort of query.  Have a look at the Agent Not Ready template which uses that method.  Essentially build up your filters as much as possible before hitting the TCD table, and when you do - use every filter that you can to refine the query.

Regards,

Jack Parker

[roberteliasson]

Thanks Jack!

I´ve recued it into one OUTER now, but Im not progressing well on the temporary table - getting this error:

Create the parameters or correct the query syntax and recreate the parameters. Incorrect syntax near the keyword 'SET'.

Any ideas on what I´m doing wrong?

SELECT TCD.DateTime, TCD.CallTypeID, TCD.WrapupData, TCD.TimeToAband

INTO #TCD

FROM Termination_Call_Detail  AS TCD

WHERE TCD.DateTime >= :startDate

AND TCD.DateTime <= :endDate

AND TCD.CallTypeID IN (:CallTypeID)

SELECT

CT.EnterpriseName,

TCD.CallTypeID,

(CASE WHEN SUM(TCD.Coded * 1.0) = 0 THEN 0 ELSE SUM(TCD.Negative * 1.0)/SUM(TCD.Coded * 1.0) END) as FailureDemand,

SUM(TCD.Negative) as Negative,

SUM(TCD.Coded) as Contacts,

sum(isnull(CTI.AnswerWaitTime,0)) as AnswerWaitTime,

sum(isnull(CTI.CallsOffered,0)) as CallsOffered, 

sum(isnull(CTI.CallsAnswered , 0))  as CallsAnswered,

sum(isnull(CTI.CallsAnswered , 0) * 1.0)/sum(isnull(CTI.CallsOffered,0) * 1.0)  as perAnswered,

MAX(isnull(TCD.max_aban_delay,0)) as max_aban_delay,

SUM(isnull(CTI.DelayQAbandTime,0))/SUM(isnull(CTI.TotalCallsAband,0)) as avg_aban_delay,

SUM(isnull(CTI.DelayQAbandTime,0)) as CallDelayAbandTime,

SUM(isnull(CTI.TotalCallsAband,0)) as TotalCallsAband

FROM

(SELECT CallTypeID,

EnterpriseName

FROM Call_Type

) CT

LEFT OUTER JOIN (SELECT

DateTime,

CallTypeID,

Negative =     Count(CASE WHEN (WrapupData LIKE 'i 04%' OR WrapupData LIKE 'i 05%' OR WrapupData LIKE 'i 06%' OR WrapupData LIKE 'i 07%') THEN WrapupData END),

Coded =     Count(CASE WHEN WrapupData LIKE 'i %' THEN WrapupData END),

max_aban_delay = MAX(TimeToAband)

FROM #TCD

WHERE DateTime >= :startDate

AND DateTime <= :endDate

AND CallTypeID IN (:CallTypeID)

GROUP BY

CallTypeID,

DateTime

) TCDz

ON TCDz.CallTypeID=CT.CallTypeID

LEFT OUTER JOIN (SELECT

CallTypeID,

DateTime,

AnswerWaitTime,  

CallsOffered,   

CallsAnswered,

TotalCallsAband,  

CallDelayAbandTime,

DelayQAbandTime

FROM Call_Type_Interval 

WHERE CallTypeID IN (:CallTypeID)

and DateTime >= :startDate

and DateTime <= :endDate

) CTI

ON CTI.CallTypeID = TCD.CallTypeID

WHERE TCD.DateTime >= :startDate

AND TCD.DateTime <= :endDate

AND CT.CallTypeID IN (:CallTypeID)

GROUP BY CT.EnterpriseName,

TCD.CallTypeID

DROP TABLE #TCD END

[jacparke · Accepted solution]

Hard to tell, I don't see a SET in there.  Typically I'll write the SQL in SQLServer Management Studio and then cut and paste.  Then the error messages seem more helpful.

I would structure this as:

; WITH CT (CallTypeID, EnterpriseName) AS  -- Smallest table first

(SELECT CallTypeID, EnterpriseName

FROM Call_Type (nolock)

WHERE CallTypeID in (:CallTypeID),

-- next smallest table

CTInt(CallTypeID, EnterpriseName, DateTime, AnswerWaitTime,   CallsOffered, CallsAnswered,TotalCallsAband, CallDelayAbandTime,DelayQAbandTime) AS

(Select stuff from Call_Type_Interval (no lock), CT

where Call_Type_Interval.CallTypeID=CT.CallTypeID

and Call_Type_Interval.DateTime between (:start) and (:end)

and ... whatever else

),

-- Final join

SELECT TCD.Stuff, CTInt.stuff

where join and filter criteria

group by

I've seen this method cut query times by 10x as compared to the more traditional structure that I'm more used to and that you've got above.

Regards,

Jack Parker

[Jameson Gagnepain]

Robert,

Normaly I would tell you if you're doing a simple query on Call_Type_Interval in CUIC, you should be able to edit the Footer for the "DelayQAbandTime" to "Max".  However, if you read about that field in the DB Schema, it's already a summary for that specific interval. It can be useful for determining an average time to abandon, but finding the individual highest abandon time is not possible with that table.

TCD is probably the only way to get that data, but I wonder why the data is needed. Bucket Intervals can provide useful information when set up correctly. If there is some maximum amount of time that you would want people to ever wait for, have your Bucket 10 start there, and provide a count of "X calls abandoned after Y minutes". Does it matter so much that a single person hung up after 30 minutes and 25 seconds if your average time to abandon is 10 minutes, or does it matter more that 24 people waited over 20 minutes?

-Jameson