Oracle SQL query when ticket moves to Resolve EXISTS? to look at actions before

I need a query to determine if an actual user looked at a ticket before it was moved to Resolved.  I've run into some issues with this b/c sometimes a user doesn't look at it but may make a rule change that affects several tickets after although once the status hits a 4 it will be gone in the future.  The only way I thought of this was running the query below for all updates to accounts that aren't resolved '4' and then for those that are '4'.  I really want to look at all the incidents before resolved and make sure a user updated it (not sysadmin).  Any suggestions? I was thinking an exist  

Also, once they are in resolved I wanted a query to determine how many days they moved in and out of resolved (if at any), how many times,  and what the reason was (incident_status_id will change) ??   If a user changes a rule sometimes they don't realize the effects on several other rules   (where the incident was't resolved for several weeks and now resolved).  So this would be a good test for all resolved although I'd want to look at it before resolved and then those rules that keep changing once a ticket is in resolved status.

I work with the business now and am having a hard time getting anything from Tech.  I somehow have read-only access to the database but can't even review the schema to see if the joins are correct and its something they haven't shared.  I found a query online  to find the scehma name but can't find anything in relation to primary keys/foreign keys.  It's an Oracle database. Any suggestions??

Thanks!!

select incident_id, incident_version_id, incident_status_id, updated_by
from dbo.incident i
where incident_status_id != '4'
abd updated_by != 'sysadmin'
group by incident_id, action_id, updated_by

Open in new window

LVL 1
rgagli1Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
can you try this :

select distinct i.incident_id
from incident i
where exists ( select
select 1
from dbo.incident ii
where ii.updated_by != 'sysadmin'
and ii.incident_id = i.incident_id and i.updated_date <= ( select min(x.updated_date)
from incident x where x.incident = ii.incident and x.status='Resolved' )
)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
A bit difficult to understand from those paragraphs. can you post the below :

1) some sample data on for few incidents and what output your query is getting currently ?
2) Also what output do you need ?

0
 
Mark GeerlingsDatabase AdministratorCommented:
If you have read-only access to the database, that should allow you to review the database objects, including table structures, indexes, foreign keys, etc.  If you don't have this much access, then either you don't have permission for the work you are trying to do, or someone in charge of this database hasn't given you the permission you need.

Even if you do have enough database access to see foreign keys, depedning on how the application was developed, there may or may not be any foerign keys defined in the database.  It is possible that the relationships are only defined in application code.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
rgagli1Author Commented:
Sample table and sample results:
table.jpg
query.jpg
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
so below is the query for the question 1). test it and let me know if that works fine for you.

select distinct i.incident_id
from incident i
where exists ( select
select 1
from dbo.incident ii
where ii.updated_by != 'sysadmin'
and ii.incident_id = i.incident_id )


0
 
Mark GeerlingsDatabase AdministratorCommented:
The suggestion from nav_kum_v may (eventually) give you the correct answer.  I'm concerned though about the performance impact of two things in that query:
1. "select distinct..."
2. "where ii.updated_by != ..."
Depending on the size of these tables (and on the amount of RAM in the server, the size of your Oracle SGA, the number of other sessions active at the time, etc.) queries that include these two features may perform rather slowly.

There are two problems with using "select distinct" in Oracle queries.  First, that always forces a sort operation on the results.  Depending on how many rows are returned, that can add a significant performance penalty.  Second, Oracle's understanding of what "distinct" means is often different from what most users expect, especially if you ever have a date column included.

The "!=" operator in a "where" clause prevents Oracle from using an index, so it forces Oracle to read *EVERY* row in the table.

That suggestion also has a syntax error in it.  It should have only one "select" after "where exists", not two.  And, the same table is referenced twice (this is legal) but one time it it referenced with the schema prefix "dbo." and the other time without.  That is inconsistent.  It will work that way, if this query is run in the DBO schema, or if there is a synonym to support this, but I don't like the inconsistency.
0
 
rgagli1Author Commented:
How do I ensure the action occurred before the ticket moved to resolved?? Wouldn't this tell me even if the action occurred after moving to resolved??

select distinct i.incident_id
from incident i
where exists ( select
select 1
from dbo.incident ii
where ii.updated_by != 'sysadmin'
and ii.incident_id = i.incident_id )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.