I need a bit of help because I can’t get the results I want (not even close) and it’s driving me crazy (well, more than I already am :))
I have an Oracle database and I use both Access and PL\SQL editor to connect with it.
This database has a view, called AUDIT that logs all the transactions of a query, taking the values from different tables.
Let me explain.
The call center receives a call from a user who has a question or a techical problem. The call center staff will record the call and the reason in the database using the software provided. Then it will pass the problem to the group of technicians that can answer or solve that problem. Everytime a technician does a job or calls the user, it will log the details.
The AUDIT view shows all these transactions and when they happened. So a regular problem could be logged as:
ID REF_NUM DATE ATTR_NAME ATTR_BEFORE ATTR_AFTER
1 223356 1449273600 New record
2 223356 1449273670 comment User said it was urgent
3 223356 1449273770 group Group1
4 223356 1449274853 assignee John Smith
5 223356 1449288006 comment Done this and that. Does not work. Investigating
6 223356 1449336001 assignee Helen Roberts
7 223356 1449360703 comment Transfer query to group2
8 223356 1449460740 group Group1 Group2
9 223356 1449460850 assignee Helen Roberts Susan Williams
And it could go on like this forever until the problem is solved and the called marked as solved.
ID Primary Key
REF_NUM is the reference number for the call (that’s the number give to the user)
DATE is a Unix timestamp when whatever transaction was logged
ATTR_NAME is the type of transaction (group transfer, comment and lots of other stuff)
ATTR_BEFORE is the previous value if existed for a particular transaction type… As an example, the problem was already assigned to a technician and then another took over, ATTR_BEFORE would be the name of the first technician. (transaction type: assignee)
ATTR_AFTER is the new value
In this case, there has been two group transfers and been assigned to three technician: two belonged to group1 and a third from group2
I need to know the time needed for a technician to assign the call/problem to someone considering that he will only see it on his screen when it has been assigned to his group.
So if I wanted to know how look John Smith took to assign the problem to himself, I would need to substract the date when it was assigned to group1 from when he assigned to himself (1449274853 - 1449274853)
Of course, in a perfect world, every single call and all its transactions would follow a certain order. Unfortunately, that’s not the case.
Also there is a second table which has the full information for the new record like who logged it, how the question was received (either user called, use the web page provided, mail, etc) and lots of other information.
Can’t seem to get a query to work well for this.
I’ve tried lots of stuff. I’ll see if I can post it later. Now I’ve got to run