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 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