Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 2 down vote accepted
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
share|improve this answer
    
"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! –  B3ret Jun 22 '11 at 7:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.