What is the most efficient way to match values ​​between two tables based on the last previous date?

Question

I have 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 the line in dailyt , I want to adjust its value by adding the corresponding value to periodt with the closest date preceding or equal to the date of the dailyt line. Thus, the result will look like this:

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 on periodt.date <= dailyt.date , and then impose the condition ROW_NUMBER() (PARTITION BY dailyt.date ORDER BY periodt.date DESC) , and then with WHERE on line number = 1.

Is there any other way to do this that would be more efficient? Or is it pretty 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; 

SQL script example

Answer 2

If there are relatively few periodt lines, then there is an option that can be quite effective.

Convert periodt to a From / To table of ranges using subqueries or CTE. (Obviously, performance depends on how efficiently this initial step can be completed, so a small number of periodt lines is periodt .) Then connecting to dailyt will be extremely efficient. For instance.

 ;WITH PIds AS ( SELECT ROW_NUMBER() OVER(ORDER BY PDate) RN, * FROM @periodt ), PRange AS ( SELECT f.PDate AS FromDate, t.PDate as ToDate, f.PVal FROM PIds f LEFT OUTER JOIN PIds t ON t.RN = f.RN + 1 ) SELECT d.*, p.PVal FROM @dailyt d LEFT OUTER JOIN PRange p ON d.DDate >= p.FromDate AND (d.DDate < p.ToDate OR p.ToDate IS NULL) ORDER BY 1 DESC 

---

If you want to try a query, the following fetch data using table variables. Note. I added an extra line to dailyt to demonstrate periodt entries with a shorter date.

 DECLARE @dailyt table ( DDate date NOT NULL, DVal float NOT NULL ) INSERT INTO @dailyt(DDate, DVal) SELECT '20140522', 10 UNION ALL SELECT '20140521', 9.5 UNION ALL SELECT '20140520', 9 UNION ALL SELECT '20140519', 8 UNION ALL SELECT '20140518', 7.5 UNION ALL SELECT '20140517', 6.5 DECLARE @periodt table ( PDate date NOT NULL, PVal int NOT NULL ) INSERT INTO @periodt SELECT '20140521', 2 UNION ALL SELECT '20140518', 1