What's the most efficient way to match values between 2 tables based on most recent prior date?

Question

I've got two tables in MS SQL Server:

dailyt - which contains daily data:

date             val
---------------------
2014-05-22       10
2014-05-21       9.5
2014-05-20       9
2014-05-19       8
2014-05-18       7.5
etc...

And periodt - which contains data coming in at irregular periods:

date             val
---------------------
2014-05-21       2
2014-05-18       1

Given a row in dailyt, I want to adjust its value by adding the corresponding value in periodt with the closest date prior or equal to the date of the dailyt row. So, the output would look like:

addt

date             val
---------------------
2014-05-22       12      <- add 2 from 2014-05-21
2014-05-21       11.5    <- add 2 from 2014-05-21
2014-05-20       10      <- add 1 from 2014-05-18
2014-05-19       9       <- add 1 from 2014-05-18
2014-05-18       8.5     <- add 1 from 2014-05-18

I know that one way to do this is to join the dailyt and periodt tables on periodt.date <= dailyt.date and then imposing a ROW_NUMBER() (PARTITION BY dailyt.date ORDER BY periodt.date DESC) condition, and then having a WHERE condition on the row number to = 1.

Is there another way to do this that would be more efficient? Or is this pretty much optimal?

Answer 1

I think using APPLY would be the most efficient way:

SELECT  d.Val,
        p.Val,
        NewVal = d.Val + ISNULL(p.Val, 0)
FROM    Dailyt AS d
        OUTER APPLY
        (   SELECT  TOP 1 Val
            FROM    Periodt p
            WHERE   p.Date <= d.Date
            ORDER BY p.Date DESC
        ) AS p;

Example on SQL Fiddle