The database is INFORMIX- Its closest relative, also by IBM, would be DB2
This is the result of summarizing a stored procedure of more than 900 lines
But if you want the schema, be my guest 
Just look the info for the very first table, AgentCallDetail. As you can see, most of the relationships are based on 4-5 fields. I don’t understand why but that’s how it is.
[quote=“SpacePhoenix, post:20, topic:115193”]( @r937 would that be the same query to get the table structure no matter what database server @molona is using) [/quote]sorry, no
SHOW CREATE TABLE is mysql only
A bit off topic, but if your working on cisco I just wanted to point out that do you have the privileges to link that document publicly here? Just to make sure you won’t get in trouble by doing so.
It is public information out there, this is a link to their own resources and you can find it in Google, so I think so, yes.
Does not really look that bad, other than it will have horrible performance 
I will come back and take a look tonight or tomorrow night after work when I have some time to digest the query.
Though, since you are actually using a Cisco service, I believe the duplicates are created by the query/subqueries, instead of being a race condition. As a side question, have you verified that the information you pull actually exist as duplicates in the said tables?
It actually takes less than 2 secs
[quote=“TheRedDevil, post:25, topic:115193”]
I believe the duplicates are created by the query/subqueries, instead of being a race condition
[/quote]It’s the table agentconnectiondetail the one that has the 13 records, so it looks like the duplicates are real
Just to clarify, is this the only agent records that is duplicated in the entire table. Or is it one of many?
This is the only agent record duplicated in the month of December 2014. I suspect that there will other duplicated in other months but that’s what I’m checking right now.
Part of the problem is you violated what I consider one of the basic tenets of SQL query design
“Never use a sub-query when a join will do”
That query is so daunting, it’s no wonder everyone is struggling to try and help. I tried to strip out all the unnecessary sub-queries and bake it down to the essentials, then added the only sub-query I thought it really needed, and which I hope solves your need (and doesn’t have too many syntax errors - it’s been nigh on forever since I did any informix work - thank goodness 
) .
SELECT DISTINCT ccd.sessionid
, ccd.sessionseqnum
, ccd.contactdisposition
, ccd.callednumber
, ccd.origcallednumber
, ccd.profileid
, ccd.nodeid
, crd.qindex
, crd.qindex
, cqdr.queuetime
, acdr.talktime
, acdr.ringtime
, acdr.holdtime
, acdr.worktime
FROM contactcalldetail ccd
, contactroutingdetail crd
, contactqueuedetail cqdr
, constactservicequeue csq
, OUTER (SELECT acd.qindex
, acd.sessionid
, acd.sessionseqnum
, acd.talktime
, acd.ringtime
, acd.holdtime
, acd.worktime
FROM agentconnectiondetail acd,
(SELECT sessionid
, MAX(ringtime)
FROM agentconnectiondetail
GROUP BY sessionid) acd2
WHERE acd.sessionid = acd2.sessionid
AND acd.ringtime = acd2.ringtime)) acdr
WHERE ccd.startdatetime BETWEEN '2013-12-1 00:00:00' AND '2013-12-31 23:59:59'
AND ccd.callednumber = '6666'
AND DATEDIFF('ss',ccd.startdatetime, ccd.enddatetime) >=10
AND cqdr.sessionid = crd.sessionid
AND cqdr.sessionseqnum = crd.sessionseqnum
AND cqdr.qindex = crd.qindex
AND cqdr.nodeid = crd.nodeid
AND cqdr.profileid = crd.profileid
AND cqdr.targetid = t.recordid
AND csq.csqname like 'CSQ_CC%'
AND ccd.sessionid = acdr.sessionid
AND ccd.sessionseqnum = acdr.sessionseqnum
AND crd.profileid = acdr.profileid
AND ccd.nodeid = acdr.nodeid
AND (Nvl(crd.qindex, acdr.qindex) = acdr.qindex or acdr.qindex <= 0)
Don’t you think I tried that? I’m trying to simplify this whole thing in just one query!
It doesn’t work
And trust me, I know it is daughting although I appreciate the help and I’m very grateful.
But it gives an error right where the OUTER is
edit: You removed the resource table for any particular reason? whenever I tried that, I got a different number
Looks like there is an extra ) before )) acdr, it should be ) acdr
That should be it. Too much copy/pasting to try to organize it…
Now it tells me that it can’t find the column ringtime
Error: Column (ringtime) not found in any table in the query (or SLV is undefined).
SQLState: IX000
ErrorCode: -217
Position: 790
Change
, MAX(ringtime)
to
, MAX(ringtime) AS ringtime
Unbelievable!
Error: Column (t) not found in any table in the query (or SLV is undefined).
SQLState: IX000
ErrorCode: -217
It seems that “t” comes from this
AND cqdr.targetid = t.recordid
Ugh…
I think you it’s just you needing to add these two fields to the OUTER sub-query
acd.profileid
acd.nodeid
edit:
Change the t. to constactservicequeue. as well
I think this is it. As t as an alias doesn’t seem to be defined.
Executing… but it is taking longer than normal. The original neded around 2 secs
But didn’t produce what you needed right?
No but I’m suspicious that this one is taking so long… it is more than one minute now.