I know this is a bit long but bear it with me.
I have an Informix database with thousands of records. The query I need to do needs information from four tables.
This Informix database records all the calls received and done in a call center.
TABLE A has all the information about the call itself: ID (the id is really a four field combination), start, end, duration, originator_nbr, destination_nbr…
TABLE B has information about how the called was handled: how long was in queue, how long it took to be answered, the time elapsed till the call was answered, the id of the agent who answered the call, etc
TABLE C has information about the agent: agent_id, agent_name….
TABLE E has information about the queue itself: Queue_id , Queue_name… but it doesn’t have a direct relation with TABLES A, B or C. A fifth table, TABLE D, needs to be used instead.
The relationships are as follows
TABLE A and TABLE B are related by four fields: sessionid, sessionseqnum, profileid, nodeid
TABLE A and TABLE D are related by four fields: sessionid, sessionseqnum, profileid, nodeid
TABLE D and TABLE E are related by two fields: targeted, profileid
TABLE B and TABLE Care related by only one field: resourceid
Right now, my query looks like this:
SELECT DISTINCT a.nodeid ||'-'|| a.sessionid ||'-'|| a.sessionseqnum as CODE, a.contactdisposition AS DISPOSITION, a.originatordn AS ORIGIN, a.callednumber AS DESTINATION, b.ringtime, b.talktime, a.startdatetime AS START, a.originatortype, e.csqname as QUEUE, c.resourcename as AGENT
FROM a, b, e, d, c
a.sessionid=b.sessionid AND a.sessionSeqNum=b.sessionSeqNum AND a.profileid=b.profileid AND a.nodeid=b.nodeid AND
a.sessionid=d.sessionid AND a.sessionSeqNum=d.sessionSeqNum AND a.profileid=d.profileid AND a.nodeid=d.nodeid AND
d.targetid=e.recordid AND d.profileid = e.profileid AND
(a.startdatetime >= TO_DATE('2013-1-1 00:00:00', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND
(a.startdatetime <= TO_DATE('2013-1-31 23:59:59', '%Y-%m-%d %H:%M:%S')-INTERVAL(1) HOUR TO HOUR) AND
This old style inner join is not mine, is the translation that any tool I use does when I connect using a ODBC connection to Informix.
This query works fine but it only gives me the list of calls that have been answered by an agent when the user dialled '123456789' directly. It doesn’t include the calls that were missed because all agents were busy and didn’t have the time to answer the call.
So I need to do an outer join for that… but for more information that I have searched, everything that I tried, I don’t seem to be able to include those records.