UPDATE annual changes with discontinuous dates

Question

This answer has shown me how to get annual changes from data:

UPDATE values_table as a
join  values_table as b 
ON b.date_sampled = DATE_SUB(a.date_sampled, INTERVAL 1 YEAR)
set a.annual_change = a.sample_value - b.sample_value

And this answer has shown me how to find the closest date to an INTERVAL (relative to NOW() with 3 results, in this question's case):

SELECT event_id FROM Table ORDER BY ABS( DATEDIFF( EVENT_START_DATE, NOW() ) ) LIMIT 3

How can the two be combined to get annual rates of change when the dates have holes, SELECTing the closest date to the desired INTERVAL?

I finally had a chance to try Gordon's answer, but I'm getting Error in query (1054): Unknown column 'vt.date_sampled' in 'where clause'.

I also tried to do a subquery based upon the second answer above after ON b.date_sampled =, but it gives Error in query (1064): Syntax error near 'SELECT date_sampled FROM values_table ORDER BY ABS( DATE_SUB(a.date_sampled, INT'.

Gordon Linoff · Accepted Answer · 2013-06-29T23:45:08.933

MySQL makes it hard to reference the update table in the update statement, but it is possible using subqueries.

I think of the "nearest date one year ago" as a good candidate for a correlated subquery:

UPDATE values_table vt
    set vt.annual_change = vt.sample_value - 
                           (select sample_value
                            from (select sample_value, date_sampled
                                  from values_table vt2
                                  where vt2.date_sampled <= DATE_SUB(vt.date_sampled, INTERVAL 1 YEAR) 
                                 ) t
                            order by date_sampled desc
                            limit 1
                           )

I would think that you would actually want the date that is at least a year old. But if you want the closest date, the same idea works:

UPDATE values_table vt
    set vt.annual_change = vt.sample_value - 
                           (select sample_value
                            from (select sample_value, date_sampled
                                  from values_table vt2
                                  where vt2.date_sampled <= DATE_SUB(vt.date_sampled, INTERVAL 1 YEAR) 
                                 ) vt2
                            order by ABS( DATEDIFF(vt.date_sampled, vt2.date_sampled))
                            limit 1
                           )

This is the closest date _before_ one year earlier. Couldn't the date closest to one year earlier be after it? — Barmar, Jun 12 '13 at 16:32
@Barmar . . . You are right. That is what the OP asked for. Just in my experience, when doing year-over-year comparisons, I would normally want at least one year. — Gordon Linoff, Jun 12 '13 at 16:43
sorry to come back so late, but I keep getting `Error in query (1054): Unknown column 'vt.date_sampled' in 'where clause'` — , Jun 29 '13 at 01:46

UPDATE annual changes with discontinuous dates

1 Answers1

Linked