Oracle DB:: Finding the first record

Hi everyone,

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:

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
02/01/2017 8:00		COMMENT					Transferring to group Installations
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.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.