SQL Join on Nearest less than date

sql, sql-server, sql-server-2005

Solution

I believe this subquery will do it (not tested).

select *, 
   (select top 1 Discount 
    from table2 
    where table2.Date <= t.Date 
    order by table2.Date desc) as Discount
from Table1 t

Perhaps not the most performant however.

Edit:

Test code:

create table #table1 ([date] datetime, val int)
create table #table2 ([date] datetime, discount int)

insert into #table1 ([date], val) values ('1/26/2010', 10)
insert into #table1 ([date], val) values ('1/25/2010', 9)
insert into #table1 ([date], val) values ('1/24/2010', 8)
insert into #table1 ([date], val) values ('1/24/2010', 9)
insert into #table1 ([date], val) values ('1/23/2010', 7)
insert into #table1 ([date], val) values ('1/22/2010', 10)
insert into #table1 ([date], val) values ('1/21/2010', 11)

insert into #table2 ([date], discount) values ('1/26/2010', 2)
insert into #table2 ([date], discount) values ('1/23/2010', 1)
insert into #table2 ([date], discount) values ('1/20/2010', 0)

select *, 
   (select top 1 discount 
    from #table2 
    where #table2.[date] <= t.[date]
    order by #table2.[date] desc) as discount
from #table1 t

drop table #table1
drop table #table2

Results:

2010-01-26 00:00:00.000 10  2
2010-01-25 00:00:00.000 9   1
2010-01-24 00:00:00.000 8   1
2010-01-24 00:00:00.000 9   1
2010-01-23 00:00:00.000 7   1
2010-01-22 00:00:00.000 10  0
2010-01-21 00:00:00.000 11  0

Problem

Normally I would just do this in the code itself, but I am curious if this can be accomplished efficiently in TSQL. ``` Table 1 Date - Value ``` ``` Table 2 Date - Discount ``` Table 1 contains entries for each day. Table 2 contains entries only when the discount changes. A discount applied to a value is considered valid until a new discount is entered. Example data: ``` Table 1 1/26/2010 - 10 1/25/2010 - 9 1/24/2010 - 8 1/24/2010 - 9 1/23/2010 - 7 1/22/2010 - 10 1/21/2010 - 11 ``` ``` Table 2 1/26/2010 - 2 1/23/2010 - 1 1/20/2010 - 0 ``` What I need returned is the following: `T1 Date - T1 Value - T2 Discount` Example data: ``` 1/26/2010 - 10 - 2 1/25/2010 - 9 - 1 1/24/2010 - 8 - 1 1/24/2010 - 9 - 1 1/23/2010 - 7 - 1 1/22/2010 - 10 - 0 1/21/2010 - 11 - 0 ``` Possible or am I better off just continuing to do this in the code?

Original source