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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
rgagli1Author Commented:
Sample table and sample results:
table.jpg
query.jpg
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Naveen KumarProduction 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.