Re: SQL help

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
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);

[...]

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 Thu Oct 07 2004 - 06:39:49 CEST

Original text of this message