I know I should know how to do this but for some reason, I can't manage to make this work.
I have two tables: INCIDETS and LOG
The first has informlation about when a user has an issue or request that needs to be resolved.
So, let's say, I wanted to install Adobe Photoshop in my office computer. I would call the call center and reuqest the installation, and they would log my request in the database.
The main structure of this table (INCIDENTS) is
REF_NUM (the reference number of the incident)
USER_NAME (the name of the user reporting the issue)
CLOSING_DATE (when the problem is resolved, the issue is closed and set to 'solved' status)
Then I have another table (LOG) where all the stepts taken to solve that issue or request are recorded.
So it would look something like this:
CHANGE_DATE ATTR_NAME BEFORE_VAL AFTER_VAL
01/01/2017 12:00 GROUP GRPADOBE
01/01/2017 12:05 ASSIGNEE SMITH, PAUL
01/01/2017 12:10 COMMENT User not available right now
01/01/2017 12:30 COMMENT James will talk to the user
01/01/2017 12:31 ASSIGNEE SMITH, PAUL ROBERTS, JAMES
02/01/2017 8:00 COMMENT Transferring to group Installations
02/01/2017 8:01 GROUP GRPADOBE GRPINSTALLATIONS
02/01/2017 8:30 ASSIGNEE BROWN, JASON
02/01/2017 12:30 COMMENT Software Installed
02/01/2017 12:35 CLOSED Incident solved
As you can see, it records all the history of a particular incident, including which support group ins in charge of the issue in a particular moment, and who is the person handling the situation.
This second table has another field, PERSID, which is for a Foreing Key of the first table. PERSID is the ID of the INCIDENTS table.
I need to find how long it took between the transfer to each group to the first assignation to anyone belonging to that group.
For example, in the case I wrote, the transfer for the group GRPADOBE was in 01/01/2017 12:00, and the first person in that support group that handled the incident was PAUL SMITH.
So the difference is 5 min only, because he was the first.
Afterwards, the incident was tranferred to GRPINSTALLATIONS and the first (and, in this case, only person) to take charnge was JASON BROWN and he took 29 min. to do so.
The reason that I need the INCIDENTS table as well is because I need to get this data for incidents opened in a specific period.
I've tried self-joins .
I've tried to use the INCIDENTS table and join the LOG table to it twice.
I've tried quite a few things, except using a sub-query as a field because I know that perfomance-wise is less than ideal.
The thing is... when there are more than one assignation, I get all the combinations possible, and not the first assignation of that particular group.
I hope that I explained myself well enough and that you can give me.