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

Question

I have a table like : trans is the name of the table for example

Id | Trans_Date          | Account_Id | Amount | Delta
------------------------------------------------------
1  | 2011-02-20 00:00:00 |     2      | 1200   | NULL
------------------------------------------------------
2  | 2011-03-21 00:00:00 |     2      | 2000   | NULL
------------------------------------------------------
3  | 2011-04-22 00:00:00 |     2      | 4500   | NULL
------------------------------------------------------
4  | 2011-02-20 00:00:00 |     4      | 1000   | NULL
------------------------------------------------------
5  | 2011-03-21 00:00:00 |     4      | 2400   | NULL
------------------------------------------------------
6  | 2011-04-22 00:00:00 |     4      | 3000   | NULL
------------------------------------------------------

And I have to update Delta column. which value is the difference of current row of the same account and preceeding row of the same account assuming there is one transaction per month. Here is a dummy sql which can generate the delta value

select tt1.id, tt1.amount , tt1.AccountId,(tt1.amount-tt2.amount) as delta 
from trans tt1 left outer  JOIN trans  tt2 
on tt1.accountid = tt2.accountid
where month(tt1.date1)-month(tt2.date1)=1 ;

The result of this query is

id | amount | AccountId  | delta  |
-------------------------------------
2  | 2000   |     2      | 800    | 
-------------------------------------
3  | 4500   |     2      | 2500   |
-------------------------------------
5  | 2400   |     4      | 1400   | 
-------------------------------------
6  | 3000   |     4      | 600    | 
-------------------------------------

But the delta of the row which has not any preceeding row should be its amount such as

1  | 1200   |     2      | 1200   | 
-----------------------------------------
4  | 1000   |     4      | 1000   | 
-----------------------------------------

these are missing by the way.

Please help me in resolving this query.

Andriy M · Accepted Answer · 2014-02-20T06:07:25.620

13

Here's your original query modified accordingly:

select
  tt1.id,
  tt1.amount,
  tt1.AccountId,
  (tt1.amount-ifnull(tt2.amount, 0)) as delta
from 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 tt1
set 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.)

edited Feb 20 '14 at 06:07

answered Feb 23 '11 at 06:08

Andriy M

76,112
17
94
154

YEAH IT WORKS, THE RESULT OF THE QUERY IS EXACTLY WHAT WE WERE LOOKING FOR...THANX ANDRIY. USING THIS DELTA VALUE , HOW CAN I UPDATE THE SAME TABLE..ITS CONFUSING...QUERYING AS WELL AS UPDATING A TABLE AT ONE RUN. IN THIS CASE, THE DELTA HAS TO BE UPDATED. – pinkb Feb 23 '11 at 07:02
Hey Andriy, Thanx a lot for your support. Actually after a little tweaking in your update script, everything works as we want. So here is tweaked version of your update scripts, 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)); – pinkb Feb 23 '11 at 07:51
I see, I've got the syntax wrong. (MySQL isn't my 'native' SQL dialect.) Thanks for the feedback. – Andriy M Feb 23 '11 at 07:59

Tim Rogers · Answer 2 · 2011-02-23T12:46:12.693

1

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

UPDATE trans
SET Delta = Amount - 
(SELECT Amount FROM trans t1
WHERE t1.Trans_Date < trans.Trans_Date
ORDER BY t1.Trans_Date DESC LIMIT 1)

edited Feb 23 '11 at 12:46

answered Feb 22 '11 at 13:58

Tim Rogers

21,297
6
52
68

Hey Tim thanx for your response. But unfortunately i am not able to execute your query successfully...mysql throws some exceptions. – pinkb Feb 22 '11 at 19:40
It's because `TOP 1` isn't the part of MySQL. The equivalent is probably `LIMIT 1`, if I'm not mistaken. – Andriy M Feb 23 '11 at 06:01
Sorry didn't see the MySQL bit. I've edited the above which *should* work for MySQL. – Tim Rogers Feb 23 '11 at 12:46

score 0 · Answer 3 · answered Feb 23 '11 at 01:56

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?

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

3 Answers3

Linked