Calculate delta(difference of current and previous row) in sql Calculate delta(difference of current and previous row) in sql sql sql

# Calculate delta(difference of current and previous row) in sql

Here's your original query modified accordingly:

``select  tt1.id,  tt1.amount,  tt1.AccountId,  (tt1.amount-ifnull(tt2.amount, 0)) as deltafrom trans tt1  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid    and month(tt1.date1)-month(tt2.date1)=1;``

The month comparison is moved from `where` to `on`, which makes a difference for `left join`, and `tt2.amount` is replaced with `ifnull(tt2.amount, 0)`.

The `UPDATE` version of the script:

``update tt1set delta = (tt1.amount-ifnull(tt2.amount, 0))from trans tt1  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid    and month(tt1.date1)-month(tt2.date1)=1;``

The correct MySQL syntax for the above update should actually be:

``update trans tt1              left outer JOIN trans tt2              on tt1.accountid = tt2.accountid              and month(tt1.date1)-month(tt2.date1)=1 set tt1.delta = (tt1.amount-ifnull(tt2.amount, 0));``

(Thanks @pinkb.)

You can use an inner query, but it's not necessarily the most efficient query.

``UPDATE transSET Delta = Amount - (SELECT Amount FROM trans t1WHERE t1.Trans_Date < trans.Trans_DateORDER BY t1.Trans_Date DESC LIMIT 1)``

Can you "union all" your query with a query that simply selects the first item for each account with the initial balance set as the delta, and the ID of that record as the id for the delta record? The result would be ordered by ID. Dirty but is it applicable?