title: Calculate delta(difference of current and previous row) in sql
body: <p>I have a table like : 
  trans is the name of the table for example</p>
  
  <pre><code>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
  ------------------------------------------------------
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>The result of this query is</p>
  
  <pre><code>id | amount | AccountId  | delta  |
  -------------------------------------
  2  | 2000   |     2      | 800    | 
  -------------------------------------
  3  | 4500   |     2      | 2500   |
  -------------------------------------
  5  | 2400   |     4      | 1400   | 
  -------------------------------------
  6  | 3000   |     4      | 600    | 
  -------------------------------------
  </code></pre>
  
  <p>But the delta of the row which has not any preceeding row should be its amount such as </p>
  
  <pre><code>1  | 1200   |     2      | 1200   | 
  -----------------------------------------
  4  | 1000   |     4      | 1000   | 
  -----------------------------------------
  </code></pre>
  
  <p>these are missing by the way.</p>
  
  <p>Please help me in resolving this query.</p>