SQL help

From: Robert Stearns <rstearns1241_at_charter.net>
Date: Wed, 06 Oct 2004 00:10:13 -0400
Message-ID: <10m6rtb9k95it9c@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.

I have not passed Cello's year to get used to thinking in sets, so I do not have the skill to write an appropriate subquery for the following specification (BTW this a small part of a much bigger query):  From owners (DDL below) we wish to select a primary owner on a given date (usually from elsewhere in the query, i.e. a birth day) say gd and a given entity_id (geid), usually related to the id of the person making the query. A primary owner is defined as follows:

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.

While I know how each part is supposed to work, I don't see how to combine them, either in SQL or as GENERATEd data items or a combination of the two.

Since this is part of a much larger query (from whence gd and geid come) I also don't see how to (and probably don't want to, for efficiency reasons) write a cursor driven function to do this either.

Any suggestion, pointers, RTFMs with precise cites etc. would be appreciated.

The owner ddl:

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

ALTER TABLE owners

   ADD CONSTRAINT bhid_fk FOREIGN KEY
(bhid)

     REFERENCES animals
      (BHID)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION
     ENFORCED
     ENABLE QUERY OPTIMIZATION;

ALTER TABLE owners

   ADD CONSTRAINT ent_fk FOREIGN KEY
(entity_id)

     REFERENCES entities_pub
      (ENTITY_ID)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION
     ENFORCED
     ENABLE QUERY OPTIMIZATION;

ALTER TABLE owners

   ADD CONSTRAINT startend CHECK
(starting <= ending)

     ENFORCED
     ENABLE QUERY OPTIMIZATION;

Received on Tue Oct 05 2004 - 23:10:13 CDT