CODEWITHSUNDEEP
sqlsql-servert-sqldatejoin
MCYeates
MCYeates

Reputation: 39

SQL Join based on dates- Table2.Date=Next date after Table1.Date

I have two seperate tables which I want to join based on Dates. However, I don't want the dates in the tables to be equal to one another I want the date (and accompanying value) from one table to be joined with the next date available after that date in the second table.

I've put an example of the problem below:

Table 1:

Date          Value
2015-04-13    A   
2015-04-10    B
2015-04-09    C
2015-04-08    D

Table 2:

Date          Value
2015-04-13    E    
2015-04-10    F
2015-04-09    G
2015-04-08    H

Desired Output Table:

Table1.Date   Table2.Date   Table1.Value   Table2.Value
2015-04-10    2015-04-13    B              E
2015-04-09    2015-04-10    C              F
2015-04-08    2015-04-09    D              G

I'm at a bit of an ends of where to even get going with this, hence the lack of my current SQL starting point!

Hopefully that is clear. I found this related question that comes close but I get lost on incorporating this into a join statment!! SQL - Select next date query

Any help is much appreciated!

M.

EDIT- There is a consideration that is important in that the day will not always be simply 1 day later. They need to find the next day available, which was in the original question but Ive update my example to reflect this.

Upvotes: 0

Views: 1551

Answers (3)

A ツ
A ツ

Reputation: 1267

i'd go with an outer apply:

SELECT t1.*, t2.*
FROM Table1 t1
CROSS APPLY (
  SELECT TOP 1 * 
  FROM  Table2 t2
  WHERE t2.Date > t1.Date
  ORDER BY t2.Date) t2
ORDER BY t1.Date DESC

Upvotes: 0

jpw
jpw

Reputation: 44881

Since you want the next available date, and that might not necessarily be the following date (eg. date + 1) you'll want to use a correlated subquery with either min or top 1.

This will give you the desired output:

;WITH src AS (
    SELECT 
       Date,
       NextDate = (SELECT MIN(Date) FROM Table2 WHERE Date > t1.Date) 
    FROM table1 t1
) 

SELECT src.Date, src.NextDate, t1.Value, t2.Value 
FROM src 
JOIN Table1 t1 ON src.Date = t1.Date
JOIN Table2 t2 ON src.NextDate = t2.Date
WHERE src.NextDate IS NOT NULL
ORDER BY src.Date DESC

Sample SQL Fiddle

Upvotes: 1

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

try this
select  [Table 1].Date,[Table 1].Value,[Table 2].date,[Table 2].Value
from    [Table 1]
join    [Table 1]
    on  dateadd(dd,1,[Table 1].date) = [Table 2].date

Upvotes: 0

Related Questions