Re: REPOST: How to search date ranges

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 29 Jan 2002 15:29:33 -0800
Message-ID: <c0d87ec0.0201291529.42b63cb7@posting.google.com>

You might want to start using plural or collective nouns for table names; unless of course you do ACTUALLY have only one person's schedule, only one event, etc.

Thanks to your lack of DDL I had to guess about keys, datatypes, constraints and DRI. You are asking people to do your job for free, and you will not give them the minimal information to do you a favor, in lieu of the their usual hourly rates; think about it. Here is my guess, in Standard SQL, at what you meant to post.

CREATE TABLE Events
(event_id CHAR(10) NOT NULL PRIMARY KEY,  start_time TIMESTAMP NOT NULL,
 stop_time TIMESTAMP nOT NULL, -- see note -- assumption about ordering
 CHECK (start_time, stop_time),
-- assumption about a natural key
 PRIMARY KEY (event_id, start_time, stop_time));

note: if an event can be open for an indefinite time, then make the end_time NULL-able. There are tricks for using COALESCE(end_time, CURRENT_TIMESTAMP) in the queries to handle this.

You called the following table "scheduled" -- why are you naming a set of entities (nouns) with a verb? You might want to look at the INCITS L8 Metadata Standards Committee naming conventions; it will help your programming quite a bit.

CREATE TABLE Schedules -- this is a relationship table (person_id INTEGER NOT NULL

          REFERENCES Personnel (person_id)
          ON UPDATE CASCADE
          ON DELETE CASCADE,
 event_id INTEGER NOT NULL
          REFERENCES Events (event_id)
          ON UPDATE CASCADE
          ON DELETE CASCADE,

 PRIMARY KEY (person_id, event_id));

"unavailable" made no sense, because it is a status and not an attribute, so I assumed I could drop it.

Oh, SQL has rows and NOT records and there is a huge difference. Records are a sequential file concept --perhaps this is why you are using cursors instead of pure SQL?

I am sorry that you are using Oracle; it is not up to SQL-92 Standards, has proprietary datatypes, screws up date formatting, etc.

>> Find all Events scheduled for each person (using
Schedules.person_id), in
the Events table by joining on the event_id, compare the start and stop
times of each event for this person to the proposed schedule times. Count
the occurrence of any overlap. I visualize it this way. The first line is the proposed schedule row. The following lines are the possible ways a scheduled event might conflict. <<

Your diagram is wrong. The SQL model of time is based on half open intervals, not on full open intervals, as you have shown. Rick Snodgrass has a book on temporal data in SQL from Morgan-Kaufmann which you might want to look at. It is VERY tricky and complex query work.

Person:

           X-----------------O
           start           stop

Events that overlap:

                 X------O
      X---------O
                           X---------O
    X-------------------------------O     <<

You would be better off looking for non-overlappping events:

Person:

              X------------O
           start           stop

Events that do not overlap:

   X------O                   X---------O

SELECT S1.person_id, S1.event_id, S2.event_id   FROM Schedules AS S1,

       Schedules AS S2,
       Events AS E1,
       Events AS E2
 WHERE S1.person_id = S2.person_id  -- same guy
   AND S1.event_id < S2.event_id    -- different Events

   AND S1.event_id = E1.event_id -- get the times    AND S2.event_id = E2.event_id
   AND NOT (S1.end_time <= S2.start_time -- open interval!!

            OR S1.start_time > S1.end_time); Received on Tue Jan 29 2002 - 17:29:33 CST