SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to find the difference between entry and exit time.

681840Oct 26 2009 — edited Oct 27 2009

Hello,

I have a table say TRACE which has three columns, Requestid, status, timestamp.

Now I have to find the difference of time stamp for distinct requestids, between one status to another.

e.g rows in table are as below

Requestid   | statusid | ENTRYDATE
A12          | 0       |31.07.2009 14:35:25
A12          | 1       |31.07.2009 16:35:25
A12          | 2       |31.07.2009 17:35:25
A12          | 3       |31.07.2009 18:35:25
A12          | 4       |31.07.2009 19:35:25

Now if i have to find the time taken by Request A12 from status 0, what shall i do. Currently I am using a select query something like

select
    (p.ENTRYDATE - s.ENTRYDATE) as Duration
  from
  TRACE  p
  ,TRACE s
where
  p.Requestid=s.Requestid   
  and s.statusid = 0 --- <--- statusid can come from bindvariable
  and p.STATUSID=6

Is there a better way to do this?

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 24 2009

Added on Oct 26 2009

#date, #pl, #sql

9 comments

734 views