I have the data and I have the result, I just don't have the query.
Date: Mon, 24 Nov 2003 12:02:47 +0300
Message-ID: <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.
Regards,
Diego Berge.
-- e-mail address invalid, please reply to newsgroups.Received on Mon Nov 24 2003 - 10:02:47 CET