Durations calculated from two rows

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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top