Need help formulating a query

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.

On 29 Sep 2005 11:26:26 -0700, sk wrote:

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

Hi sk,

To begin with the latter: Normalize - alert_description should probably
go to a table alert_types, as it's functionally dependent on the
alert_code. Include constraints (PRIMARY KEY, UNIQUE, NOT NULL and a
CHECK constraint). Use PascalCase for column names as well as table
names and get rid of under_scores. And consider if you really need to
store chinese characters in the alert_description; if extended ASCII
will do, use varchar instead of nvarchar.

CREATE TABLE Alerts
(
DeviceID varchar(17) NOT NULL,
AlertCode int NOT NULL,
GenerationDate datetime NOT NULL,
RemovalDate datetime DEFAULT NULL,  -- NULL = not removed yet
PRIMARY KEY (DeviceID, AlertCode, GenerationDate),
UNIQUE (DeviceID, AlertCode, RemovalDate),
FOREIGN KEY (AlertCode) REFERENCES AlertTypes (AlertCode),
FOREIGN KEY (DeviceID) REFERENCES Devices (DeviceID),
CHECK (GenerationDate <= RemovalDate),
)


And here's the query that will show you the desired output. Note that I
didn't test it; see www.aspfaq.com/5006 if you prefer a tested solution.

-- First, create a view so that we don't have
-- to code the same logic twice in the main query
CREATE VIEW dbo.StartDates
AS
SELECT     a.DeviceID, a.GenerationDate AS From
FROM       Alerts AS a
WHERE NOT EXISTS
(SELECT   *
FROM     Alerts AS b
WHERE    b.DeviceID = a.DeviceID
AND      b.GenerationDate < a.GenerationDate
AND      COALESCE(b.RemovalDate, '99991231') > a.GenerationDate)
go
-- And here's the real query
SELECT     a.DeviceID, a.From,
 NULLIF(MAX(COALESCE(
b.RemovalDate, '99991231')), '99991231')
AS To
FROM       StartDates AS a
INNER JOIN Alerts AS b
ON   b.DeviceID = a.DeviceID
AND  b.GenerationDate >= a.From
AND  COALESCE(b.RemovalDate, '99991231')
< ALL (SELECT   From
FROM     StartDates AS c
WHERE    c.DeviceID = a.DeviceID
AND      c.From  > a.From)
GROUP BY   a.DeviceID, a.From

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis  wrote:
> On 29 Sep 2005 11:26:26 -0700, sk wrote:
>

<snip>

>
> Hi sk,
>
> To begin with the latter: Normalize - alert_description should probably
> go to a table alert_types, as it's functionally dependent on the
> alert_code. Include constraints (PRIMARY KEY, UNIQUE, NOT NULL and a
> CHECK constraint). Use PascalCase for column names as well as table
> names and get rid of under_scores. And consider if you really need to
> store chinese characters in the alert_description; if extended ASCII
> will do, use varchar instead of nvarchar.
>

Thank you, these are all helpful, except for the casing, I had the rest
of it pretty much covered. (yes, I do need the nvarchar for 4
languages, including Chinese)

> CREATE TABLE Alerts
> (
>    DeviceID varchar(17) NOT NULL,
>    AlertCode int NOT NULL,
>    GenerationDate datetime NOT NULL,
>    RemovalDate datetime DEFAULT NULL,  -- NULL = not removed yet
>    PRIMARY KEY (DeviceID, AlertCode, GenerationDate),
>    UNIQUE (DeviceID, AlertCode, RemovalDate),
>    FOREIGN KEY (AlertCode) REFERENCES AlertTypes (AlertCode),
>    FOREIGN KEY (DeviceID) REFERENCES Devices (DeviceID),
>    CHECK (GenerationDate <= RemovalDate),
> )
>
>
> And here's the query that will show you the desired output. Note that I
> didn't test it; see www.aspfaq.com/5006 if you prefer a tested solution.
>

I am sure that I can make it work easily after you did all the hard
work, and looks like it will work anyway.  This is precisely what I was
looking for.

Thank you for all your help, Hugo.


> -- First, create a view so that we don't have
> -- to code the same logic twice in the main query
> CREATE VIEW dbo.StartDates
> AS
> SELECT     a.DeviceID, a.GenerationDate AS From
> FROM       Alerts AS a
> WHERE NOT EXISTS
>  (SELECT   *
>   FROM     Alerts AS b
>   WHERE    b.DeviceID = a.DeviceID
>   AND      b.GenerationDate < a.GenerationDate
>   AND      COALESCE(b.RemovalDate, '99991231') > a.GenerationDate)
> go
> -- And here's the real query
> SELECT     a.DeviceID, a.From,
>             NULLIF(MAX(COALESCE(
b.RemovalDate, '99991231')), '99991231')
>                                         AS To
> FROM       StartDates AS a
> INNER JOIN Alerts AS b
>       ON   b.DeviceID = a.DeviceID
>       AND  b.GenerationDate >= a.From
>       AND  COALESCE(b.RemovalDate, '99991231')
>            < ALL (SELECT   From
>                   FROM     StartDates AS c
>                   WHERE    c.DeviceID = a.DeviceID
>                   AND      c.From  > a.From)
> GROUP BY   a.DeviceID, a.From
>