Checking query for calls presented

[christina.singer1]

Hello,

I have reviewed at length the posted schema and query information for the UCCX database.  I was hoping someone could review my query below and let me know if I am missing anything.  I am trying to get a count of all calls presented for today so far (grouped by csq).  Thanks in advance for the help.

SELECT contactservicequeue.csqname,

contactqueuedetail.profileid, contactqueuedetail.targetid, COUNT(distinct contactcalldetail.sessionid)

AS [Calls Presented]

FROM UCCX.db_cra.informix.contactcalldetail CROSS JOIN

UCCX.db_cra.informix.contactqueuedetail CROSS JOIN

UCCX.db_cra.informix.contactservicequeue

WHERE (contactcalldetail.sessionseqnum = contactqueuedetail.sessionseqnum) AND (contactcalldetail.sessionid = contactqueuedetail.sessionid) AND

(contactcalldetail.profileid = contactqueuedetail.profileid) AND (contactcalldetail.nodeid = contactqueuedetail.nodeid) AND (contactqueuedetail.targetType = 0) AND

(contactqueuedetail.targetid = contactservicequeue.recordid) AND (contactcalldetail.startdatetime >= CONVERT(date, GETDATE()))

GROUP BY contactqueuedetail.targetid, contactqueuedetail.profileid, contactservicequeue.csqname

[Aaron Harrison]

It didn't work for me... I guess it didn't for you?

SELECT contactservicequeue.csqname,

contactqueuedetail.profileid, 

contactqueuedetail.targetid, 

COUNT(distinct contactcalldetail.sessionid) AS CallsPresented

FROM contactcalldetail 

JOIN contactqueuedetail

ON  (contactcalldetail.sessionseqnum = contactqueuedetail.sessionseqnum) 

AND (contactcalldetail.sessionid = contactqueuedetail.sessionid) 

AND (contactcalldetail.profileid = contactqueuedetail.profileid) 

AND (contactcalldetail.nodeid = contactqueuedetail.nodeid)

JOIN contactservicequeue

ON (contactqueuedetail.targetid = contactservicequeue.recordid) 

WHERE  (contactcalldetail.startdatetime >= EXTEND(current, YEAR TO DAY))

AND (contactqueuedetail.targetType = 0)
 
GROUP BY contactqueuedetail.targetid, contactqueuedetail.profileid, contactservicequeue.csqname

I changed:

• [Calls Presented] to CallsPresented
• Changed the order of your = clauses so that the ones that relate to the joins are with the joins, and the ones for the 'where' were with the where
• Changed CONVERT(date, GETDATE()) to EXTEND(current, YEAR TO DAY)
• Removed the owner/prefix stuff from the table names
• Formatted it so keywords are at the start of the row generally, it's easier to spot missing commas and doubled keywords etc that way in my view...

Regards

Aaron

[christina.singer1]

Aaron,

Thanks for taking a look at the query.  However, I am still showing a higher number of calls presented than I should.  Any suggestions on how to fix the query.  Am I missing a join or grouping?

Thanks!
 

Christina

[Aaron Harrison]

Hi

It looks OK for me today... you'll need to test it to see what 'extra' rows are being pulled... e.g.

SELECT contactservicequeue.csqname,

contactqueuedetail.profileid, 

contactqueuedetail.targetid, 

 contactcalldetail.sessionid

FROM contactcalldetail 

JOIN contactqueuedetail

ON  (contactcalldetail.sessionseqnum = contactqueuedetail.sessionseqnum) 

AND (contactcalldetail.sessionid = contactqueuedetail.sessionid) 

AND (contactcalldetail.profileid = contactqueuedetail.profileid) 

AND (contactcalldetail.nodeid = contactqueuedetail.nodeid)

JOIN contactservicequeue

ON (contactqueuedetail.targetid = contactservicequeue.recordid) 

WHERE  (contactcalldetail.startdatetime >= EXTEND(current, YEAR TO DAY))

AND (contactqueuedetail.targetType = 0)
 
--GROUP BY contactqueuedetail.targetid, contactqueuedetail.profileid, contactservicequeue.csqname

That will show a row for each call, you can then add in some extra columns to see which rows are 'duplicates'... 

What are you comparing it against?

Aaron

[christina.singer1]

Hi Aaron,

I am comparing against a historical report (run at the same time the query is run) and also the supervisor feature for Informix.  Even when I pull all the rows in for the query shown above I am not showing duplicate values.  Sometimes the query results match the live supervisor stats from Informix but mostly they are off by about 5-10 calls.  I feel like I am missing something....not sure what it is.  I am not new to SQL but I am new to Informix. Im unsure as to what the fix is.

-Christina

[Aaron Harrison]

So are you running the historical report for calls to the app, or to the CSQ?

Do your calls queue against multiple CSQs in some scenarios?

Aaron

[christina.singer1]

Im running the Contact service queue activity historical report to check the stats.  In some instances a call may be queued to multiple CSQs, yes.

-Christina

[Aaron Harrison]

OK - so I took the query from that report, and removed the filtering, and added CSQ names... this looks like so:

SELECT cqdr.targetid, csq.csqname, cqdr.profileid, Count(cqdr.sessionid)
            , (Sum(cqdr.queuetime) / Count(cqdr.sessionid)), Max(cqdr.queuetime)
       FROM Contactqueuedetail cqdr, Contactcalldetail ccdr, contactservicequeue csq         
       WHERE cqdr.sessionid = ccdr.sessionid AND
             cqdr.sessionseqnum = ccdr.sessionseqnum AND
             cqdr.profileid = ccdr.profileid AND
             cqdr.nodeid = ccdr.nodeid AND
             ccdr.startdatetime > EXTEND(current, YEAR TO DAY) AND
               cqdr.targetid = csq.recordid AND
             cqdr.targettype = 0
       GROUP BY cqdr.targetid, csq.csqname, cqdr.profileid;

Any different?

[christina.singer1]

Hi Aaron,

Thanks again for your quick responses.  Tested your query out.  Here is what is interesting: For some of the queues the calls presented shows the exact same number as what shows in the historical report.  For other queues the number is off by as many as 12 calls.  It's baffling to me.  I can tell the query is close to pulling the right number of calls but I feel like there is a variable that is missing.

[Aaron Harrison]

Do you open 24 hours?

[Aaron Harrison]

Do you open 24 hours?

I'm wondering if it's a time offset thing... E.g. if you run the HRC report, and take a look at the actual query (in the log files) what times does it submit?

Times in the DB are in UTC, so the query I posted would return all calls since 00:00 UTC today. If you're in the US and run 24 hours, then you may have taken calls before then, and call that 'today'.

HRC offsets the times submitted with the queries...

Maybe try this for an example of 5 hours back from UTC (adjust that to wherever you are..):

SELECT  cqdr.targetid, csq.csqname, cqdr.profileid, Count(cqdr.sessionid)
           , (Sum(cqdr.queuetime) / Count(cqdr.sessionid)), Max(cqdr.queuetime)
       FROM Contactqueuedetail cqdr, Contactcalldetail ccdr, contactservicequeue csq         
       WHERE cqdr.sessionid = ccdr.sessionid AND
             cqdr.sessionseqnum = ccdr.sessionseqnum AND
             cqdr.profileid = ccdr.profileid AND
             cqdr.nodeid = ccdr.nodeid AND
             ccdr.startdatetime > (EXTEND(EXTEND(current, YEAR TO DAY),YEAR TO SECOND) - 5 UNITS HOUR ) AND
               cqdr.targetid = csq.recordid AND
             cqdr.targettype = 0
      GROUP BY cqdr.targetid, csq.csqname, cqdr.profileid;
  

Aaron

[christina.singer1]

You make a good point with the UTC time! I did not realize that fact.  I adjusted your query to the one below. The extend function does not work for me so I used a different one.:

SELECT cqdr.targetid, csq.csqname, cqdr.profileid, Count(cqdr.sessionid)

, (Sum(cqdr.queuetime) / Count(cqdr.sessionid)), Max(cqdr.queuetime)

FROM UCCX.db_cra.informix.Contactqueuedetail cqdr, UCCX.db_cra.informix.Contactcalldetail ccdr, UCCX.db_cra.informix.contactservicequeue csq

WHERE cqdr.sessionid = ccdr.sessionid AND

cqdr.sessionseqnum = ccdr.sessionseqnum AND

cqdr.profileid = ccdr.profileid AND

cqdr.nodeid = ccdr.nodeid AND

ccdr.startdatetime > DATEADD (hour, -5, getdate() ) AND

cqdr.targetid = csq.recordid AND

cqdr.targettype = 0

GROUP BY cqdr.targetid, csq.csqname, cqdr.profileid

order by csqname;

[Aaron Harrison]

Hi 

.. and was it better? :-)
 

Aaron

[christina.singer1]

Hi Aaron,

Yes, much better! Thanks so much for all your time and help.  :)

[Aaron Harrison]

Ah good :-)

Please remember to rate useful responses and mark questions 'answered' when appropriate to highlight useful content...