By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,997 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,997 IT Pros & Developers. It's quick & easy.

Need help formulating a query

P: n/a
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_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.

Sep 29 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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)
Sep 29 '05 #2

P: n/a
sk

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


Sep 29 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.