I have two tables like so:
T1_Key, T1_Data, T1_ValidUntil
1 A 2000-01-01
1 B 2000-06-30
2 C 2005-05-31
3 D 2004-12-31
3 E 2007-04-30
3 F 2008-01-31
T2_Key, T2_Data, T2_ValidUntil
1 R 2002-03-31
2 S 2001-06-30
2 T 2003-02-28
2 U 2005-05-31
3 V 2006-09-30
3 W 2007-06-30
This defines a number of intervals where specific data is valid. For example for key 2 and date 2002-01-01, the entries (2, C, 2005-05-31) and (2, T, 2003-02-28) are valid (i.e., the entries with the next larger date). For key 2 and date 2010-01-01 no entry is valid.
For a single query like above, I just query both tables seperately and join the data in my application. However, for a maintenance application I'd like to join these two tables. The result should contains all valid intervals/combinations, like so (Key 3 is the most complex one, because there are no shared dates):
T1_Key, T1_Data, T1_ValidUntil, T2_Key, T2_Data, T2_ValidUntil
1 A 2000-01-01 1 R 2002-03-31
1 B 2000-06-30 1 R 2002-03-31
2 C 2005-05-31 2 S 2001-06-30
2 C 2005-05-31 2 T 2003-02-28
2 C 2005-05-31 2 U 2005-05-31
3 D 2004-12-31 3 V 2006-09-30 <- I1
3 E 2007-04-30 3 V 2006-09-30 <- I2
3 E 2007-04-30 3 W 2007-06-30 <- I3
3 F 2008-01-31 3 W 2007-06-30 <- I4
ASCII-Art vor Case 3 (Timelines):
2004-12-31 2007-04-30 2008-01-31
T1 -----|-----------------|-----------------------|----------
I1 I2 I3 I4 Nothing valid here
T2 ---------------|-----------------|--------------------------
2006-09-30 2007-06-30
Currently I simply join using the keys, but that gives a lot of combinations which are not valid. I therefore need a way to say "Join this with the entry having the next-higher (or equal) date in the other table", and a way to do that in both directions…
The scheme is predefined and I can not change it very much. A third many-to-many table seems like redundant data for me.
Thanks in advance!
Best Answer