SQL Server: join two tables with "valid-to" dates

Question

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!

Answer 1

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)
INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')
INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')
SELECT
 T1.*, T2x.*
FROM
 @t1 T1
 CROSS APPLY
 (SELECT TOP 1*
 FROM @t2 
 WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
 ORDER BY T2_ValidUntil
 ) T2x
UNION
SELECT
 T1x.*, T2.*
FROM
 @t2 T2
 CROSS APPLY
 (SELECT TOP 1*
 FROM @t1
 WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
 ORDER BY T1_ValidUntil
 ) T1x

• B3ret: "CROSS APPLY" was the keyword, I didn't know it. The example works like a charm and I could apply it to my problem. I will ask a new related, but different followup question. Thank you very much!