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.

[Back to original message]