|
|
Posted by sk on 09/29/05 21:26
I have a table for storing alerts (exceptional situations) occuring on
devices that I monitor. Associated with each alert is an alert code, a
description, the device responsible for causing the alert, when the
alert was generated, and when the alert was removed (device no longer
had the alert)
A candidate table definition looks like
CREATE TABLE Alerts
(
device_id varchar(17),
alert_code int,
alert_description nvarchar(128),
generation_date datetime,
removal_date datetime
-- constraints, etc not shown, generation_date <= removal_date
)
What I want to figure out is, on a device by device basis, determine
contiguous periods of time when the device was in alert.
For example, if the above table had these entries for a device:
alert1 10:20 to 10:23
alert2 10:25 to 10:40
alert3 10:28 to 10:29
alert4 10:41 to 11:45
alert5 11:44 to 12:31
Then, I want a query that will help me determine
that the device had the following periods where one or more alerts were
active
10:20 to 10:23
10:25 to 10:40
10:41 to 12:31
Any help would be appreciated, including suggestions on designing the
table differently.
Navigation:
[Reply to this message]
|