Re: SQL help

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);

[...]

One way is to so something like:

with suspects_1 (bhid, entity_id, starting, duration) as (

    select bhid, entity_id, starting, (ending - starting) seconds     from owners
    where starting<=gd AND (ending IS NULL OR ending>=gd) ), suspects_2 (bhid, entity_id, starting, percent) as (

    select s1.bhid, s1.entity_id, s1.starting, o.percent     from suspects_1 s1, owner o
    where (s1.bhid, s1.entity_id, s1.starting) = (o.bhid, o.entity_id, o.starting)       and starting = (select MIN(starting) from suspects_1) ), suspects_3 (bhid, entity_id, starting) as (

    select bhid, entity_id, starting
    from suspects_2
    where percent = (select min(percent) from suspects_2) ...

HTH
/Lennart Received on Wed Oct 06 2004 - 23:39:49 CDT