'How to attribute value to row based on nearest date

I'm attempting to attribute a value for "revenue" to a row based on the closest run_date, looking forwards in time. I'm using a date range as a join condition to only join rows within a 10 day "lookahead" period with the below....

SELECT 
* 
FROM t1 
  INNER JOIN t2 ON t1.id = t2.id 
  AND t2.date BETWEEN t1.run_date AND DATE_ADD(t1.run_date, INTERVAL 10 DAY)

The far right table is what I'm trying to achieve:

enter image description here

Does anybody have any advice as to how to get my intended result? (Far right table of the image)

I have a SQL fiddle here if anyone is interested in helping out.

sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions