CODEWITHSUNDEEP
sql-server
Arash Ghasemi Rad
Arash Ghasemi Rad

Reputation: 314

SQL SELECT between two tables join by date

I have two tables: 1- Settings

EffectiveDate  ||     Value
==============================
'2000-01-01'   ||       2
'2005-01-01'   ||       4
'2010-01-01'   ||       6

2- DataTable

      ID       ||  RecordDate
===============================
       1       ||  '2001-01-01'
       2       ||  '2002-01-01'
       3       ||  '2003-01-01'
       4       ||  '2007-01-01'
       5       ||  '2008-01-01'
       6       ||  '2011-01-01'
       7       ||  '2013-01-01'
       8       ||  '2014-01-01'

the relation between these two table is that EffectiveDate from Settings most be smaller(and/or equal) than RecordDate from DataTable but must be the newest one. for example the result must be something like this:

      ID       ||  RecordDate   || value
========================================
       1       ||  '2001-01-01' ||   2
       2       ||  '2002-01-01' ||   2
       3       ||  '2003-01-01' ||   2
       4       ||  '2007-01-01' ||   4
       5       ||  '2008-01-01' ||   4
       6       ||  '2011-01-01' ||   6
       7       ||  '2013-01-01' ||   6
       8       ||  '2014-01-01' ||   6

thank you for your help :D

Upvotes: 2

Views: 60

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use CROSS APPLY and TOP to achieve the desired result:

SELECT
    dt.ID, dt.RecordDate, x.Value
FROM DataTable dt
CROSS APPLY(
    SELECT TOP 1 Value
    FROM Settings s
    WHERE s.EffectiveDate <= dt.RecordDate
    ORDER BY s.EffectiveDate DESC
) x

ONLINE DEMO

Upvotes: 3

Related Questions