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)
