UPDATE Annual Changes With Discontinuous Dates

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.

Solution 1:

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
                           )