Need help formulating a query

[sk]

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

[Hugo Kornelis]

Re: Need help formulating a query

On 29 Sep 2005 11:26:26 -0700, sk wrote:
[color=blue]
>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_descripti on 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.[/color]

Hi sk,

To begin with the latter: Normalize - alert_descripti on 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_descripti on; 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.GenerationDat e AS From
FROM Alerts AS a
WHERE NOT EXISTS
(SELECT *
FROM Alerts AS b
WHERE b.DeviceID = a.DeviceID
AND b.GenerationDat e < a.GenerationDat e
AND COALESCE(b.Remo valDate, '99991231') > a.GenerationDat e)
go
-- And here's the real query
SELECT a.DeviceID, a.From,
NULLIF(MAX(COAL ESCE(b.RemovalD ate, '99991231')), '99991231')
AS To
FROM StartDates AS a
INNER JOIN Alerts AS b
ON b.DeviceID = a.DeviceID
AND b.GenerationDat e >= a.From
AND COALESCE(b.Remo valDate, '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)

[sk]

Re: Need help formulating a query


Hugo Kornelis wrote:[color=blue]
> On 29 Sep 2005 11:26:26 -0700, sk wrote:
>[/color]

<snip>
[color=blue]
>
> Hi sk,
>
> To begin with the latter: Normalize - alert_descripti on 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_descripti on; if extended ASCII
> will do, use varchar instead of nvarchar.
>[/color]

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)
[color=blue]
> 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.
>[/color]

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.

[color=blue]
> -- 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.GenerationDat e AS From
> FROM Alerts AS a
> WHERE NOT EXISTS
> (SELECT *
> FROM Alerts AS b
> WHERE b.DeviceID = a.DeviceID
> AND b.GenerationDat e < a.GenerationDat e
> AND COALESCE(b.Remo valDate, '99991231') > a.GenerationDat e)
> go
> -- And here's the real query
> SELECT a.DeviceID, a.From,
> NULLIF(MAX(COAL ESCE(b.RemovalD ate, '99991231')), '99991231')
> AS To
> FROM StartDates AS a
> INNER JOIN Alerts AS b
> ON b.DeviceID = a.DeviceID
> AND b.GenerationDat e >= a.From
> AND COALESCE(b.Remo valDate, '99991231')
> < ALL (SELECT From
> FROM StartDates AS c
> WHERE c.DeviceID = a.DeviceID
> AND c.From > a.From)
> GROUP BY a.DeviceID, a.From
>[/color]