0

I'm working on an Informix query that wiil 1) Give me a list of call center agents and 2) give me their most recent status. I have a query that does almost everything I need;

select b.resourcename, b.extension, a.eventtype
from agentstatedetail as a, resource as b, team as c
where date (eventdatetime) = TODAY
  and (a.agentid = b.resourceid)
  and (b.assignedteamid = c.teamid)
  and (c.teamname like 'Team Name %')
group by b.resourcename, b.extension, a.eventtype
order by resourcename asc

However, this will give me a complete record of "eventtype" (eventtype shows the states an agent was in at a given time). I'm trying to pull only the most recent state. So, I believe I need to do a subquery to this main query stating something like, "If the main query's output is found in this subquery, then keep it". The subquery looks like this;

select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime)
from agentstatedetail as a, resource as b, team as c
where date (eventdatetime) = TODAY
  and a.agentid = b.resourceid
  and b.assignedteamid = c.teamid
  and c.teamname like 'Team Name %'
group by a.agentid, b.resourcename, c.teamname

This subquery, by itself, is almost everything I need, but it doesn't have the eventtype. How would this query work?

This is the format that I typically write subqueries in but can't make work in Informix;

select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime) as eventdatetime, a.eventtype from agentstatedetail as a, resource as b, team as c where date(eventdatetime) = TODAY and a.agentid=b.resourceid and b.assignedteamid=c.teamid and c.teamname like 'ITS %' and where a.eventdatetime in (select a.agentid, b.resourcename, c.teamname, max(a.eventdatetime) from agentstatedetail as a, resource as b, team as c where date(eventdatetime) = TODAY and a.agentid=b.resourceid and b.assignedteamid=c.teamid and c.teamname like 'Team Name %' group by a.agentid, b.resourcename, c.teamname) group by a.agentid, b.resourcename, c.teamname, a.eventtype order by max(a.eventdatetime) desc
Kimomaru
  • 993
  • 4
  • 14
  • 30

1 Answers1

2

Try doing an inner join with that subquery and agentstatedetail on agentid and the date that you found out that was the max:

select m.agentid, m.resourcename, m.teamname, a2.eventtype
from (
  select a.agentid,b.resourcename,c.teamname,max(a.eventdatetime) as maxtdate
  from agentstatedetail as a
  inner join resource as b on a.agentid = b.resourceid
  inner join team as c on b.assignedteamid = c.teamid
  where date (eventdatetime) = TODAY
    and c.teamname like 'Team Name %'
  group by a.agentid, b.resourcename, c.teamname
  ) m
inner join agentstatedetail a2 
    on a2.agentid = m.agentid and a2.eventdatetime = m.maxtdate

I'm a little confused on the columns that you want to see at the end. If these are not it, you will have to change them in the inner and outer queries.

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Thanks, Filipe. This statement works, although I am not use to doing inner joins. I like to do full joins using a subquery specifying, for instance, "where eventtype not in (select . . . )". I tried to write it, but Informix kicked it back. Do you know how, if possible, it can be written? – Kimomaru Dec 09 '13 at 17:58
  • 1
    You are most certainly used to doing inner joins, just not the explicit way. :). In your original query you had: `...from agentstatedetail as a, resource as b, team as c where a.agentid = b.resourceid and b.assignedteamid = c.teamid ...`. These are INNER JOINs using the "old fashion" way. See [here](http://stackoverflow.com/q/1018822/1385896) for a better explanation. – Filipe Silva Dec 09 '13 at 18:03
  • Interesting. I will look into this. Thanks, Filipe. – Kimomaru Dec 09 '13 at 18:08
  • Filipe, I've edited the question with the format that I'm used to writing the queries in but doesn't seem to work in Informix. Can you see the mistake? – Kimomaru Dec 09 '13 at 18:27
  • 1
    @Kimomaru. That query has a lot of errors, but even correcting the most obvious, you might not get the correct information and it will perform much worse than if you did it like i told you in my answer. And using IN is going to be way harder to write. – Filipe Silva Dec 09 '13 at 18:32
  • Interesting. Maybe it's an Informix thing? I've written queries like these before in mySQL and haven't had problems (I'm very new to Informix). – Kimomaru Dec 09 '13 at 18:39
  • 1
    Some problems: You have two where clauses in the same subquery, you have an and before the second where, IN can only have one column in the right (you have 4 returning). and even after this, it wouldn't give you what you want. – Filipe Silva Dec 09 '13 at 18:44
  • Okay, I'll take a closer look. Thanks, Filipe. – Kimomaru Dec 09 '13 at 18:46