how to achieve the following task using sql server management studio -

i have 2 datasets. dataset1 have 2 variable name , date. dataset1 have name, date2 , price.

dataset1 looks following

name  date x1    2013-04-12 x1    2013-05-12  x1    2014-04-12 x2    2012-06-11 x2    2014-04-12 x2    2015-05-01  x3    2005-02-01 

dataset2 looks following

name  date1         price x1    2013-04-11     1.1 x1    2013-05-12     2 x1    2014-04-13     3 x2    2012-06-13     2.2 x2    2014-04-12     0.6 x2    2016-05-01     0.5 

for each name , date in dataset1, want find price of same name recent (latest) date1 equal or earlier date dataset1.name = dataset2.name , max(date1 |date1<= date).

result should looks like:

name  date          date1          price x1    2013-04-12    2013-04-11     1.1 x1    2013-05-12    2013-05-12     2 x1    2014-04-12    2013-05-12     2 x2    2012-06-11    null           null x2    2014-04-12    2014-04-12     0.6 x2    2015-05-01    2014-04-12     0.6 x3    2005-02-01    null           null 

can teach me how code please.

in sql server, easiest method outer apply:

select d1.*, d2.* dataset1 d1 outer apply      (select top 1 d2.*       dataset2 d2       d2.name = d1.name , d2.date1 <= d1.date       order d2.date1 desc      ) d2;