Posted by Hugo Kornelis on 09/30/05 00:54

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)

[Back to original message]