Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Informix / SQL Query to calculate Service Level Met %

Hi,

We have a client running UCCX 7.0 and we are upgrading it to 8.5.

They have a custom .net application that display different stats on IP Phone. I have converted all of them except i am having problem calculating Service Level Met %

Existing SQL query converts boolean into Float but Informix gives error that boolean can't be converted to float or integer.

here is part of existing query (SQl Server) to calculate service level met %

ROUND((SUM(CAST(ssl.metServiceLevel AS FLOAT))/COUNT(CAST(ssl.metServiceLevel AS FLOAT))) * 100,2) AS serviceLevelMet

I will greatly appreciate if anyone can help converting this query into Informix query format.

Thank you

Sonain
1 ACCEPTED SOLUTION

Accepted Solutions
Silver

Informix / SQL Query to calculate Service Level Met %

Try this:

SELECT csqname, ROUND(sum(100 * (MetSL)/ (case when (MetSL + NotSL  = 0) then 1 else (MetSL + NotSL) end)) ) as servicelevel

FROM(

        SELECT

               trim( csqname) csqname

              ,sum( case cqd.metServiceLevel when 't' then  1 else 0 end)  MetSL

              ,sum( case cqd.metServiceLevel when 't' then 0 else 1 end) NotSL

        FROM ContactServiceQueue csq

            inner join contactqueuedetail cqd  on cqd.targetID = csq.recordID

            inner join contactroutingdetail crd on crd.sessionID = cqd.sessionID

            inner join contactcalldetail ccd on crd.sessionID = ccd.sessionID

        WHERE Date(current) - Date(crd.StartDateTime ) = 0

        GROUP BY csqname

    )z

GROUP BY z.csqname

Please rate helpfull posts.

Graham

4 REPLIES
Silver

Informix / SQL Query to calculate Service Level Met %

Try this:

SELECT csqname, ROUND(sum(100 * (MetSL)/ (case when (MetSL + NotSL  = 0) then 1 else (MetSL + NotSL) end)) ) as servicelevel

FROM(

        SELECT

               trim( csqname) csqname

              ,sum( case cqd.metServiceLevel when 't' then  1 else 0 end)  MetSL

              ,sum( case cqd.metServiceLevel when 't' then 0 else 1 end) NotSL

        FROM ContactServiceQueue csq

            inner join contactqueuedetail cqd  on cqd.targetID = csq.recordID

            inner join contactroutingdetail crd on crd.sessionID = cqd.sessionID

            inner join contactcalldetail ccd on crd.sessionID = ccd.sessionID

        WHERE Date(current) - Date(crd.StartDateTime ) = 0

        GROUP BY csqname

    )z

GROUP BY z.csqname

Please rate helpfull posts.

Graham

New Member

Informix / SQL Query to calculate Service Level Met %

Hi Graham,

Thank you for taking time to post query. I will try it tomorrow in my app and will let you know how it goes.

Once again thank you,

Sonain

Sonain
New Member

Informix / SQL Query to calculate Service Level Met %

Hi Garaham,

I have tried to incorporate provided query with existing one but i am having little problem.

may be you can guide me

my question is how i am going to join "LEFT JOIN RtCSQsSummary rtcsq ON rtcsq.CSQName = csq.CSQName"  so that i can get rest of the information like    "rtcsq.loggedInAgents, rtcsq.talkingAgents, rtcsq.workingAgents, rtcsq.reservedAgents,rtcsq.

availableAgents"

Thank you

Sonain

Sonain
Silver

Informix / SQL Query to calculate Service Level Met %

Hi Sonain

Try this, it won't display a service level until you have received at least one call on the CSQ.

SELECT csqsum.CSQName,loggedInAgents,talkingAgents,availableAgents,workingAgents,reservedAgents,servicelevel

FROM  RtCSQsSummary csqSum left outer join

     (SELECT csqname, ROUND(sum(100 * (MetSL)/ (case when (MetSL + NotSL  = 0) then 1 else (MetSL + NotSL) end)) ) as servicelevel

        FROM(

            SELECT

                 trim( csqname) csqname

                ,sum( case cqd.metServiceLevel when 't' then  1 else 0 end)  MetSL

                ,sum( case cqd.metServiceLevel when 't' then 0 else 1 end) NotSL

            FROM ContactServiceQueue csq

                inner join contactqueuedetail cqd  on cqd.targetID = csq.recordID

                inner join contactroutingdetail crd on crd.sessionID = cqd.sessionID

                inner join contactcalldetail ccd on crd.sessionID = ccd.sessionID

            WHERE Date(current) - Date(crd.StartDateTime ) = 0

            GROUP BY csqname

        )z

   GROUP BY z.csqname

    )y

ON y.CSQName = csqsum.CSQName

Please rate helpfull posts.

Graham

1111
Views
0
Helpful
4
Replies
CreatePlease to create content