hello list, Michael Thygs is right, but the expression can be simplified. We have to assume that all dates are in correct order, i. e. for each date pair the following relation is true:
from date <= to date When a date range from tab1 does NOT overlap a date range from tab2, then the tab1 range is either entirely before or entirely after the tab 2 range. "tab1 entirely before tab2" can be expressed by: tab1.date_to < tab2.date_from and it is not necessary to test tab1.date_from < tab2.date_from because tab1.date_from should be <= date1.date_to. Similariy, "tab1 entirely after tab2" can be expressed by: tab1.date_from > tab2.date_to For the WHERE clause, we have to exclude both these cases (I do this here by inverting the relationship and ANDing the two expressions): WHERE (tab1.date_to >= tab2.date_from) and (tab1.date_from <= tab2.date_to) AFAIK, this should result in all overlapping date ranges being selected. > -----Original Message----- > From: Michael Thygs [mailto:[EMAIL PROTECTED]] > Sent: Friday, October 05, 2001 1:29 PM > To: [EMAIL PROTECTED] > Subject: AW: Date interval problem > > > Hello Bruno, > > its a little bit complicated. You have to put a little bit of > brain in it! > > The situation you ask for is: > > tab2.interval <---------------------------> > possible intervals of table 1, which should be displayed are: > 1. <-----------------------> > 2. <------------------> > 3. > <--------------------------> > 4. > <-----------------------------------------------------------> > > Now you have to write four parts of where-clauses which > describe each of > this situtation and you have to connect them with an OR > > select * from tab1, tab2 where (where clause for situation 1) > OR (where > clause for situation 1) OR ... > > and the where clauses look like this (situation 1): > (tab1.date_from <= tab2.date_from AND tab1.date_from <= > tab2.date_to) AND > (tab1.date_to >= tab1.date_from AND tab1.date_to <= tab2.dat_from) > > and so on with situation 2,3 and 4. > > Maybe it isn´t a efficient way, but it should be possible. > > Greetings > > Michael > > PS: Don´t forget the brakets > > > -----Ursprüngliche Nachricht----- > Von: Bruno Grampa [mailto:[EMAIL PROTECTED]] > Gesendet: Freitag, 5. Oktober 2001 12:34 > An: [EMAIL PROTECTED] > Betreff: Date interval problem > > > Hello, > i have a table with two dates, that define an interval. > In the query i have two more dates, and i want to select all > the records > that overlap that period. > Any hint to solve this problem? > > Thanks, > Bruno > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
