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...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.