How to attribute value to row based on nearest date

user_6067528 0
Viewed 53

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
1 Answers

t1 needs to be compared to itself in order to find the t1 row where t2 belongs. Do do so you need to join to t1 twice. You can put a sub-select to test for the existence of a more appropriate t1 into the condition of an OUTER JOIN as follows:

SELECT 
t1.id, c_id, run_date, coalesce(revenue,0) revenue
FROM t1 
  LEFT OUTER JOIN t2 ON t1.id = t2.id 
  AND t2.date BETWEEN t1.run_date AND DATE_ADD(t1.run_date, INTERVAL 10 DAY)
  AND NOT EXISTS (
    SELECT 'X'
    FROM t1 as later
    WHERE t1.id = t2.id
    AND later.run_date < t2.date
    AND t1.run_date < later.run_date
)
ORDER BY run_date
user_73595020
Related