Ask the Expert

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!


    Requires Free Membership to View

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
inner
  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 first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: