Oracle: HBelp with basic query for evaluating a number pass/fail

Hello Experts,

I am wanting to write a query that will evaluate the time difference between the time an alarm was presented to the time a ticket was created, (I have that part).   What I am need is to to evaluate the duration to see if it went over 5 minutes, if it did then "Fail", else "Pass".

The issue is that I wanting to evaluate it in a single case statement.  The reason is because I have other evalautions to write and don't want to nest select statments yet.

Here is what I have so far.
Evaluator.txt
Maliki HassaniAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
select --....,
  round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) "seconds elapsed"
, case when round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) > (5*60) then 'Fail' else 'Pass' end min_5_test
, trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')), 24)) || 'd ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24, 24)) || 'h ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60, 60)) || 'min ' ||
  trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60 * 60, 60)) || 'sec' "time elapsed"
from dual --....

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
select ....,
round((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 86400) "seconds elapsed",
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')), 24)) || 'd ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24, 24)) || 'h ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60, 60)) || 'min ' ||
       trunc(mod((sysdate - to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')) * 24 * 60 * 60, 60)) || 'sec' "time elapsed"
from ....

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
substitute "sysdate" with "ALARM_DATE" and "to_date('29.08.2008 11:24:16', 'dd.mm.yyyy hh24:mi:ss')" with TICKET_CREATE_DATE
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Maliki HassaniAuthor Commented:
Let me give this a try.. Thanks
0
 
Maliki HassaniAuthor Commented:
I appreciate the code, but my query gives me the minutes correctly.  I am looking to have it evalaute it the 5 minutes and produce the results as Pass or fail.
0
 
Maliki HassaniAuthor Commented:
Trying now, thanks
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
but my query gives me the minutes correctly.  I am looking to have it evalaute it the 5 minutes and produce the results as Pass or fail.

I don't get that, please explain again ;-)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
ignore my last post, didn't read PortletPaul's response...
0
 
Maliki HassaniAuthor Commented:
Thank you!
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.