Problem solve Get help with specific problems with your technologies, process and projects.

Durations calculated from two rows

I am trying to calculate the duration of an event. The start of the event is one row in a table and the end of...

the event is another row (later or lower in the table). An event starts when a device, A, records an alarm entry, "Comm", with a severity-of-5. The event is over when the same device records the same alarm with a severity of 1.

Here is my data set:

edate   dev alarm  severity
11:06AM  A  Comm     5
11:07AM  B  Standby  5
11:08AM  C  Comm     5
11:16AM  A  Comm     1
11:27AM  C  Comm     1
11:28AM  A  Standby  5
11:37AM  B  Standby  1
11:45AM  B  Comm     5
11:45AM  C  Standby  5
11:46AM  A  Standby  1
12:05PM  B  Comm     1
12:06PM  C  Standby  1

This is what I would like to have as a result:

dev alarm   start    end
 A  Comm    11:06AM 11:16AM
 B  Standby 11:07AM 11:37AM
 C  Comm    11:08AM 11:27AM
 A  Standby 11:28AM 11:46AM
 B  Comm    11:45AM 12:05PM
 C  Standby 11:45AM 12:06PM

Thanks in advance!

This requires a self-join with a condition:

select t1.dev
     , t1.alarm
     , t1.edate as start
     , t2.edate as "end"
  from events as t1
  join events as t2
    on t1.dev   = t2.dev
   and t1.alarm = t2.alarm
   and t2.edate =
       ( select min(edate)
           from events
          where dev      = t1.dev
            and alarm    = t1.alarm
            and severity = 1 
            and edate    > t1.edate )
 where t1.severity = 5

The reason you need the MIN subquery is in case there is more than one set of start/end pairs for each device.

This was last published in March 2005

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.