Wallboard SLA query

I wanted to see how hard it would be to get SLA information to display on my wallboard. I can up with the following two queries that i thought i would share with you all if anyone is interested.

UCCX SLA Query all queues (UCCX 8.X and above)

SELECT
        RTRIM(csqSum.CSQName) CSQ,
        loggedInAgents,
        availableAgents
        unavailableAgents,
        totalCalls,
        (callsHandled + callsAbandoned + callsDequeued) callsOffered,
        callsHandled,
        ((MetLevel * 100) / callsHandled) ServiceLevel,
        DECODE(1 , DECODE( 1 ,DECODE( totalCalls ,0 ,1,0) , 1 , 0 ) ,0,(callsHandled * 100 / totalCalls)) percentCallsHandled,
        callsAbandoned,
        (avgWaitDuration / 1000) as avgWaitDuration,
        callsWaiting CustomersWaiting,
        DECODE(1 , DECODE( 1 ,DECODE( callsHandled + callsAbandoned ,0 ,1,0) , 1 , 0 ) ,0,(100 * callsAbandoned) / (callsHandled + callsAbandoned)) percentAbandoned
FROM  RtCSQsSummary csqSum,
    (    SELECT
             CSQNAme,
             SUM(MetLevel) MetLevel
    FROM (        SELECT  RTRIM(CSQName) CSQName,
        SUM(CASE WHEN cqd.metServiceLevel then 1 else 0 end) MetLevel
        FROM  ContactRoutingDetail crd,
             ContactQueueDetail cqd,
             ContactServiceQueue csq,
             ContactCallDetail ccd 
        WHERE     crd.sessionID  = cqd.sessionID
         AND    (cqd.targetID  = csq.recordID)
         AND    (crd.sessionID  = ccd.sessionID)
         AND    crd.StartDateTime >= TODAY
         AND     cqd.metServiceLevel
        GROUP BY  CSQName
) x 
    GROUP BY  x.CSQNAme 
) y 
WHERE     y.CSQName = csqsum.CSQName
ORDER BY CSQ 

UCCX SLA Query Single Queue (UCCX 8.X and Above)

SELECT
        RTRIM(csqSum.CSQName) CSQ,
        loggedInAgents,
        availableAgents
        unavailableAgents,
        totalCalls,
        (callsHandled + callsAbandoned + callsDequeued) callsOffered,
        callsHandled,
        MetLevel CallsMetSla,
        ROUND(((MetLevel * 100) / callsHandled),0) ServiceLevelPercent,  
        DECODE(1 , DECODE( 1 ,DECODE( totalCalls ,0 ,1,0) , 1 , 0 ) ,0,(callsHandled * 100 / totalCalls)) percentCallsHandled,
        callsAbandoned,
        (avgWaitDuration / 1000) as avgWaitDuration,
        callsWaiting CustomersWaiting,
        DECODE(1 , DECODE( 1 ,DECODE( callsHandled + callsAbandoned ,0 ,1,0) , 1 , 0 ) ,0,(100 * callsAbandoned) / (callsHandled + callsAbandoned)) percentAbandoned
FROM  RtCSQsSummary csqSum,
    (    SELECT
             CSQNAme,
             SUM(MetLevel) MetLevel
    FROM (        SELECT  RTRIM(CSQName) CSQName,
        SUM(CASE WHEN cqd.metServiceLevel then 1 else 0 end) MetLevel
        FROM  ContactRoutingDetail crd,
             ContactQueueDetail cqd,
             ContactServiceQueue csq,
             ContactCallDetail ccd 
        WHERE     crd.sessionID  = cqd.sessionID
         AND    (cqd.targetID  = csq.recordID)
         AND    (crd.sessionID  = ccd.sessionID)
         AND    crd.StartDateTime >= TODAY
         AND     cqd.metServiceLevel
        GROUP BY  CSQName
) x 
    GROUP BY  x.CSQNAme 
) y 
WHERE     y.CSQName = csqsum.CSQName AND y.CSQName = 'VMSUPPORT'

 

What this will produce is a result set that has the following headers

CSQ

LoggedinAgents

UnavailableAgents

TotalCalls

CallsOffered

CallsHandled

ServiceLevel

PercentageCallsHandled

CallsAbandoned

avgWaitDuration

CustomersWaiting

PercentageAbandoned

Still a bit of a work in progress and take about 1.5 seconds to run so i think needs some improvement

Enjoy

About these ads