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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.