Oracle Query: can't get the right results

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 :slight_smile:

Sorry. Also for the long post. Still, I do need to go on.

As I mentioned briefly, there’s a second table where some important data is logged when there’s a new issue. This table, CALL_REQ, shares the REF_NUM with AUDIT in a 1 to many relatioinship (although REF_NUM is not a primary key, each recond in CALL_REQ will only be related with one reference number)

CALL_REQ has the OPENING_DATE (that should be the same of the “New record” file in AUDIT view), a TYPE field that indicates the type of question ask (technical problem, query, etc) and a CATEGORY field wich is a numeric value.

As I said, I need to be able to know how long it takes for the tech people in one group to assign the job to one of them, once it has been given to their group, in a certain period of time (monthly, yearly…)

What I’ve done is to select all the records that have been transferred to the group I want with their dates. Then a query selecting all records that were assigned to any member of that group.

I used an INNER JOIN on those two queries to get only those REF_NUM that had been assigned to a group and to a person in that group (that is, there’s no mistakes)

Each of those queries is JOINED with CALL_REQ to specify the period, and to get those REF_NUM that are not logged as errors (CATEGORY 67) and TYPE=‘I’ (issue)

This is one of my tries (unfortunately, for some reason I didn’t copy the last one)

SELECT t.date AS fecha
     , t.ATTR_NAME
     , t.attr_after
     , t.ref_num
     , c.open_date
 FROM 
  ((SELECT * FROM audit WHERE attr_name='group')
           INNER JOIN (SELECT * from AUDIT WHERE attr_name='assignee') AS a2 ON a2.ref_num=a1.ref_num) AS t 
           INNER JOIN call_req AS c ON t.ref_num= c.ref_num
    WHERE 
           (((t.attr_after)='group1') 
           AND ((c.type)='I') AND ((c.category)<>67) 
           AND ((c.open_date) Between 1420070400 And 1451347199)) 
      OR 
           (((t.attr_after) In ('Smith, John','Williams, Sarah ','Turner, Louise','Doe, John ','Martin, Rob ')) 
          AND ((c.type)='I') 
          AND ((c.category)<>67) 
          AND ((c.open_date) Between 1420070400 And 1451347199)) 
      OR 
           (((t.attr_after)='CAU -OFIMATICA') AND ((c.type)='I') 
           AND ((c.category) Is Null) 
          AND ((c.open_date) Between 1420070400 And 1451347199))
       OR 
           (((t.attr_after) In ('Smith, John','Williams, Sarah ','Turner, Louise','Doe, John ','Martin, Rob ')) 
           AND ((c.type)='I') 
           AND ((c.category) Is Null) 
           AND ((c.open_date) Between 1420070400 And 1451347199)

I added a last condition, of course, and that is that the minimum date for a2 (assgined to a person) has to be greater or equal than the date for a1 (assigned to the group). but the thing is that, for some reason, it dosn’t always work.

Sometimes, the time for transferring to the group and assigning to one technician is the same because the software allows you to do it in one step. Yet, the query result, for some reason, finds when the next technician took over, not when it was taken by the first.

What you really need is a table that associates the assignee to a group. Do you not have that information? Deducing the group based on the first preceding group value by time is a very brittle and dirty means of doing what you want. Not to mention I don’t think you will be able to do that with a a single query. I would almost rather use a trigger to add that piece of information as a value to the table after an assignee is inserted. At least you than have the value in the table and don’t need to resort to such cryptic and brittle methods for the calculation. Trigger is the way I would go here to add the data as a new row after inserting an assignee.

I wish! There is a contact table which tell you which type of contact you are (user, technitian and groups, yes, the groups are listed like any user) but I couldn’t find the way to know which contact (type technician) belonged to which group :frowning:

I have read-only permission. I can’t add anthing or create temp tables in Oracle. I could do something in Access connection to Oracle though

That seems like an unreasonable limitation given the structure of the database and problem which needs to be solved. Could you add logic at the application level instead of a trigger. When an assignee is inserted find the the most recent group value and insert another key value pair with the data you’re after.

The AUDIT view was created so we could track transactions as we don’t have access to the whole database.

Although this is just one query, the end result would be Access. It is just that any query in Access, for reasons that I don’t know, takes ages. So I’m trying to query the database with PL/SQL which provides results much faster.

And this is what I have to work with. No that I had much saying :smiley:

What you have to work with is clearly unacceptable for your task. In fact I think it is the responsibility of the query that builds the view to provide this data. Unless you have access to those tables and can query them to get the data you need. There probably is a way to do this with the data you have but you have to make a lot of unreasonable assumptions to do so.

1 Like

Thanks for the moral support :smiley:

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