SQL Join on Nearest less than date

Question

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?

Joel · Accepted Answer · 2010-01-26T22:04:01.737

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

score 31 · Answer 2 · answered Jan 26 '10 at 22:14

31

No "nearest" query is going to be as efficient as an "equals" query, but this is another job for the trusty ROW_NUMBER:

;WITH Discounts_CTE AS
(
    SELECT
        t1.[Date], t1.[Value], t2.Discount,
        ROW_NUMBER() OVER
        (
            PARTITION BY t1.[Date]
            ORDER BY t2.[Date] DESC
        ) AS RowNum
    FROM Table1 t1
    INNER JOIN Table2 t2
        ON t2.[Date] <= t1.[Date]
)
SELECT *
FROM Discounts_CTE
WHERE RowNum = 1

answered Jan 26 '10 at 22:14

Aaronaught

120,909
25
266
342

1

+1: Good idea. It would be interesting to see the bench mark of subquery vs. join with row_number. – Joel Jan 26 '10 at 22:27
In my case, with SQLite and ~2000 rows, subquery was more than 10 times faster – Alex Kosh Nov 21 '21 at 08:38
This query fails to produce the expected Value=9 record for 2010-01-24 (with SQL Server Management Studio 19.1.56.0). It does produce the other expected records. – Louis Strous Jun 20 '23 at 07:59

score 8 · Answer 3 · answered Sep 25 '19 at 22:27

8

Adding to Joels answer... if you have IDs present in both tables, the following will improve performance:

select *, 
   (select top 1 Discount 
    from Table2 t2
    where t2.Date <= t1.Date 
    and t2.ID = t1.ID 
    order by t2.Date desc) as Discount
from Table1 t1

answered Sep 25 '19 at 22:27

xxyjoel

551
5
7

3

"Joels Answer" may change or disappear. Write your answers to stand alone. – HackSlash Sep 25 '19 at 23:01
1

@HackSlash (1) "joels answer" is the accepted answer and it is highly unlikely it will disappear; referring to someone else's answer is not an uncommon practice. (2) the answer is standalone...? – xxyjoel May 31 '21 at 20:47
1

If you want to add to Joel's answer, just click "Edit" on his answer and add to it. Stack Overflow is a wiki. You can edit other people's questions and answers. – falsePockets Sep 08 '21 at 02:56
Worked nicely for me. – glass_kites Sep 13 '22 at 13:08

score 1 · Answer 4 · answered May 06 '20 at 21:23

This is a typical scenario for asof join. In DolphinDB, one can directly use asof jointo solve this problem efficiently.

Test code:

table1 = table(2010.01.26 2010.01.25 2010.01.24 2010.01.24 2010.01.23 2010.01.22 2010.01.21 as date,  10 9 8 9 7 10 11 as val)
table2 = table(2010.01.26 2010.01.23 2010.01.20 as date, 2 1 0 as discount)
select date, val, discount from aj(table1, (select * from table2 order by date), `date)

score 0 · Answer 5 · answered Jan 26 '10 at 22:22

This works on oracle XE. Since sql server does have analytic functions, it shouldn't be to difficult to port it.

create table one (
    day date,
    value integer
);


create table two (
    day date,
    discount integer
);


insert into one values (trunc(sysdate), 10);
insert into one values (trunc(sysdate-1), 8);
insert into one values (trunc(sysdate-2), 1);
insert into one values (trunc(sysdate-3), 23);
insert into one values (trunc(sysdate-4), 3);
insert into one values (trunc(sysdate-5), 4);
insert into one values (trunc(sysdate-6), 8);
insert into one values (trunc(sysdate-7), 5);
insert into one values (trunc(sysdate-8),8);
insert into one values (trunc(sysdate-9), 8);
insert into one values (trunc(sysdate-10), 5);    


insert into two values (trunc(sysdate), 2);
insert into two values (trunc(sysdate-3), 1);
insert into two values (trunc(sysdate-5), 3);
insert into two values (trunc(sysdate-8), 1);


select day, value, discount, cnt,
    nvl(max(discount) over (partition by cnt) 
    ,0) as calc_discount
from (
    select day, value, discount,
        count(discount) over (order by day) as cnt
    from one
    left outer join two  
    using(day) 
)

SQL Join on Nearest less than date

5 Answers5

Linked