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

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

i have 2 seperate tables want bring together based on dates. however, don't want dates in tables equal 1 want date (and accompanying value) 1 table joined next date available after date in sec table.

i've set illustration of problem below:

table 1:

date value 2015-04-13 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 @ bit of ends of going this, hence lack of current sql starting point!

hopefully clear. found related question comes close lost on incorporating bring together statment!! sql - select next date query

any help much appreciated!

m.

edit- there consideration of import in day not 1 day later. need find next day available, in original question ive update illustration reflect this.

since want next available date, , might not next date (eg. date + 1) you'll want utilize correlated subquery either min or top 1.

this give desired output:

;with src ( select date, nextdate = (select min(date) table2 date > t1.date) table1 t1 ) select src.date, src.nextdate, t1.value, t2.value src bring together table1 t1 on src.date = t1.date bring together table2 t2 on src.nextdate = t2.date src.nextdate not null order src.date desc

sample sql fiddle

sql sql-server tsql date join