SQL Server : join number between different date

Question

I have 2 tables like this:

num   datetime    color
...   ........    .....
40   2007/07/07   red  
40   2012/12/01   white   

name   datetime   num
....   ........   ...
Tom   2008/01/22  40
Tom   2014/02/27  40

How I can do inner join on those tables like this?

name       date     num   color
....   ..........   ...  ......
Tom    2008/01/22   40    red
Tom    2014/02/31   40    white

Thank you!

Answer 1

I can suggest that you want all rows from second table and colors from first table where date is less than in second one and is max. If so:

DECLARE @t1 TABLE
    (
      num INT ,
      dt DATETIME ,
      color NVARCHAR(MAX)
    )
DECLARE @t2 TABLE
    (
      num INT ,
      dt DATETIME ,
      name NVARCHAR(MAX)
    )

INSERT  INTO @t1
VALUES  ( 40, '20070707', 'red' ),
        ( 40, '20121201', 'white' )

INSERT  INTO @t2
VALUES  ( 40, '20080122', 'Tom' ),
        ( 40, '20140228', 'Tom' )

SELECT  t2.name ,
        t2.dt ,
        t2.num ,
        c.color
FROM    @t2 t2
        CROSS APPLY ( SELECT TOP 1
                                t1.color
                      FROM      @t1 t1
                      WHERE     t2.num = t1.num
                                AND t1.dt < t2.dt
                      ORDER BY  t1.dt DESC
                    ) c

Output:

name    dt                      num color
Tom     2008-01-22 00:00:00.000 40  red
Tom     2014-02-28 00:00:00.000 40  white