Search code examples
mysqldatedelta

Calculating differences with SQL

I have been doing some research on this subject for a while, and thanks to a solution posted in another topic, I got close to solving this issue.

I am attempting to get the changes in a column of data: row(n) - row(n-1)

update Table tt1 
left outer JOIN Table tt2 
on tt1.name = tt2.name 
and tt1.date-tt2.date=1 
set tt1.delta = (tt1.amount-ifnull(tt2.amount, tt1.amount));

Output is

Date      |    Value   |    Delta
2013-03-30|    38651   |   393
2013-03-31|    39035   |   384
2013-04-01|    39459   |   0
2013-04-02|    39806   |   347

As you can see, the difference does not calculate for the first of April (the rest of the values are just fine). The same happens for the 1st day of every month.

My guess is that there is something to do with [and tt1.date-tt2.date=1], but I can't figure out exactly what.

Thanks for all your help in advance!

Solution

  • I made some changes to your statement... your error is either on the way you handle the dates or in the way you handle the delta...

    update Table tt1 
left outer JOIN Table tt2 
on tt1.name = tt2.name 
and tt1.date = date_sub(tt2.date, interval 1 day)    
set tt1.delta = case when tt2.amount is not null then tt1.amount - tt2.amount else -1 end;