Re: SQL help
Date: 6 Oct 2004 21:39:49 -0700
Message-ID: <6dae7e65.0410062039.3d963943_at_posting.google.com>
Robert Stearns <rstearns1241_at_charter.net> wrote in message news:<10m6rtb9k95it9c_at_corp.supernews.com>...
> This is cross posted because I honestly do not know which of the two
> news groups is more appropriate or more likely to yield a reply.
>
[...]
>
> The longest owner: MIN(starting) AND starting<=gd AND (ending IS NULL OR
> ending>=gd)
> If there ties, MAX(percent) among them
> If there are still ties, entity_id=geid if it exists
> else longest membership: MIN(entity_id)
> The last is guaranteed to be unique.
[...]
>
> CREATE TABLE owners
> (
> bhid BIGINT NOT NULL,
> entity_id INTEGER NOT NULL,
> starting DATE NOT NULL,
> percent DECIMAL(7, 4) NOT NULL DEFAULT 100,
> ending DATE
> )
> DATA CAPTURE NONE;
>
> ALTER TABLE owners
> ADD CONSTRAINT primary PRIMARY KEY
> (bhid, entity_id, starting);
[...]
HTH
/Lennart
Received on Thu Oct 07 2004 - 06:39:49 CEST