Re: I have the data and I have the result, I just don't have the query.

"Diego Berge" <dberge_at_privacy.net> wrote in message news:bpshfo$1s6t46$1_at_ID-186969.news.uni-berlin.de...
> Hi there,
>
> I'm scratching my head over the following problem.
>
> Suppose I have a table of events, which specifies event type, start
> and end times, like so:
>
> CREATE TABLE events (
> event_type INT NOT NULL REFERENCES types (event_type),
> dt_start DATETIME NOT NULL,
> dt_end DATETIME, -- NULL means event goes on forever
> KEY (event_type) -- event_type is not unique
> );
>
> Example:
> +------------+------------+------------+
> | event_type | dt_start | dt_end |
> +------------+------------+------------+
> | 20 | 2002-06-24 | 2005-06-24 |
> | 24 | 2001-02-12 | NULL |
> | 25 | 2002-04-01 | 2005-04-01 |
> | 25 | 2003-05-24 | 2006-05-24 |
> | 26 | 2003-05-24 | 2004-05-24 |
> | 31 | 2003-05-24 | NULL |
> | 40 | 2003-05-24 | NULL |
> | 42 | 2001-08-30 | 2006-08-30 |
> +------------+------------+------------+
>
> Then I have a table of "conditions", a condition being defined as a
> number of events occurring simultaneously:
>
> CREATE TABLE conditions (
> condition_id INT NOT NULL,
> event_type INT NOT NULL REFERENCES types (event_type),
> PRIMARY KEY (condition_id, event_type)
> );
>
> Example:
> +--------------+------------+
> | condition_id | event_type |
> +--------------+------------+
> | 6 | 20 |
> | 6 | 26 |
> | 6 | 42 |
> +--------------+------------+
>
> From that information, I should be able to determine the *times*
> during which a condition is present. So, in the example above, I should
> be able to say that condition 6 is present between 2003-05-24 and
> 2004-05-24, as those are the dates during which all of events 20, 26,
> and 42 are occurring. One must keep in mind that conditions could be
> present at various discontinuous points in time, and that two or more
> events of the same type could be present simultaneously (e.g., 25 above).
>
> Now, I don't seem to be able to come up with an adequate SQL
> statement that will give me the above information. I was trying to get
> something such as:
>
> +--------------+------------+------------+
> | condition_id | dt_start | dt_end |
> +--------------+------------+------------+
> | 6 | 2003-05-24 | 2004-05-24 |
> +--------------+------------+------------+
>
> I would very much appreciate any assistance that you could offer on
> this one. TIA.

Seek out information on relational division. Celko has probably posted solutions on more than one occasion. Received on Mon Nov 24 2003 - 15:02:40 CET