|
|
Posted by sk on 09/30/05 02:43
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
>
Navigation:
[Reply to this message]
|