Re: Time difference between records

"Bricklen" <bricklen_at_zyahoo.zcomz> wrote in message news:G3E1d.25909$XP3.135_at_edtnps84...
| Hans Forbrich wrote:
| > Scott Johnson wrote:
| >
| >
| >>I need to find a method (sql, plsql) to find the time difference between
| >>records
| >>
| >>Example,
| >>Ticket# Operation Time
| >>A1 Modify 1000
| >>A1 Modify 1100
| >>A1 Modify 1300
| >>A2 Modify 0900
| >>A2 Modify 0930
| >>
| >>Should produce something like:
| >>
| >>A1 1 hour
| >>A1 2 hours
| >>A2 30 minutes
| >>
| >>Has anyone done this before?
| >
| >
| > 1) Ain't going nowhere if you don't capture a 'modified date/time'
stamp.
| >
| > 2) Different database version provide different capabilities to help
here.
| >
| > 3) In 10g look up the time interval datatype, otherwise look up date
| > arithmetic, both in the SQL Reference manual.
|
| I'm thinking that what he wants is more along the lines of "connect by",
| or lag/lead & analytic functions etc.
|
| There are a few sql gurus on this group that can probably answer this
| question. There are also many examples in the archives where this sort
| of question has already been answered. Also relevant is the Oracle
| version...

if you only have the time data value, you will not get reliable results for any ticket that spans a day

do you have to stick with the current table structure or can you modify it? are there other columns you're not showing, such as an ID which is guaranteed to be assigned in order or a date or timestamp column?

do any tickets span a day? (that is span midnight, not just last longer than 24 hours)

++ mcs Received on Tue Sep 14 2004 - 10:32:04 CDT