title: sql query for Running balance
body: <p>i am still new to sql, i will need your help, i have a table that look like this</p>
  
  <pre>
  ses_date          trx_no        amount
  02-04-2014       27487776I      1000
  03-05-2014       27485776Y      -500
  01-02-2014       65474645H      4500
  09-01-2014       65474656D     -3400
  </pre>
  
  <p>and i will need an output like this</p>
  
  <pre>
  ses_date        trx_no        amount   Debit     Credit        Balance
  02-04-2014       27487776I      1000     0.00      1000.00       1000
  03-05-2014       27485776Y      -500    -500       0.00          500
  01-02-2014       65474645H      4500    0.00       4500.00       5000
  09-01-2014       65474656D     -3400    -3400.00   0.00          1600
  </pre>
  
  <p>just like a statement of account but in my own case i dont have debit and credit separately, they are together. </p>
  
  <p>Thanks so much for your help and support, you are the best. my DBMS is microsoft SQL server 2008. i try to use this query</p>
  
  <pre><code>SELECT ses_date, trx_no, amount, 
    CASE WHEN amount&lt;0 THEN amount ELSE 0 END debit,
    CASE WHEN amount&gt;0 THEN amount ELSE 0 END credit,
    (SELECT SUM(amount) FROM mytable a WHERE a.ses_date&lt;=mytable.ses_date) balance
  FROM mytable
  ORDER BY ses_date;
  </code></pre>
  
  <p>but it gives (0.00) ZERO in the balance column all through but the debit and the credit is OK. what will i do.</p>
  
  <p>and when i use the second query</p>
  
  <pre><code>select ses_date, 
         trx_no, 
         amount, 
         case
             when amount &lt; 0 then amount 
             else 0
         end as debit,
         case 
             when amount &gt;= 0 then amount
             else 0 
         end as credit,
         sum(amount) over (order by ses_date) as balance
  from the_table
  order by ses_date
  </code></pre>
  
  <p>the error is</p>
  
  <p>Msg 102, Level 15, State 1, Line 12 Incorrect syntax near 'order'.</p>
  
  <p>what will i do</p>