02-03-2015 08:32 AM - edited 03-14-2019 02:24 PM
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
02-05-2015 01:56 AM
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:
Regards
Aaron
02-05-2015 06:20 AM
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
02-05-2015 06:37 AM
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
02-05-2015 07:11 AM
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
02-05-2015 07:33 AM
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
02-05-2015 07:42 AM
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
02-05-2015 08:18 AM
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?
02-05-2015 08:29 AM
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.
02-05-2015 08:49 AM
Do you open 24 hours?
02-05-2015 09:02 AM
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
02-05-2015 11:25 AM
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;
02-06-2015 02:49 AM
Hi
.. and was it better? :-)
Aaron
02-06-2015 10:11 AM
Hi Aaron,
Yes, much better! Thanks so much for all your time and help. :)
02-10-2015 04:15 AM
Ah good :-)
Please remember to rate useful responses and mark questions 'answered' when appropriate to highlight useful content...
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide