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

"Diego Berge" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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.