title: How to get balance from transaction table
body: <p>I have transaction table on my postgresql server with schema like this</p>
  
  <pre><code>CREATE TABLE tbl_transaction
  (
    id serial NOT NULL,
    id_coa integer,
    date_trx date,
    amount numeric(15,2),
    dc character varying(1)
  );
  </code></pre>
  
  <p>And data like this</p>
  
  <pre><code>INSERT INTO tbl_transaction (id, id_coa, amount, date_trx, dc)
  VALUES
     (1, 1, 100, '2020-01-31', 'D'),
     (2, 2, 100, '2020-01-31', 'D'),
     (3, 3, 100, '2020-01-31', 'D'),
     (4, 1, 100, '2020-02-01', 'D'),
     (5, 1, 50, '2020-02-01', 'C'),
     (6, 1, 200, '2020-02-02', 'D'),
     (7, 2, 150, '2020-02-01', 'D'),
     (8, 2, 70, '2020-02-01', 'C'),
     (9, 2, 120, '2020-02-02', 'D'),
     (10, 3, 200, '2020-02-01', 'D'),
     (11, 3, 120, '2020-02-01', 'C'),
     (12, 3, 130, '2020-02-02', 'D');
  </code></pre>
  
  <p>From the data above, if I query from 2020-02-01 to 2020-02-02, I want result like this below:</p>
  
  <pre><code>| id  | id_coa | date_trx   |beginning_balance| debit  | credit |
  | --- | ------ | ---------- |-----------------| ------ | ------ |
  | 4   | 1      | 2020-02-01 | 100.00          | 100.00 | 0      |
  | 5   | 1      | 2020-02-01 | 200.00          | 0      | 50.00  |
  | 6   | 1      | 2020-02-02 | 150.00          | 200.00 | 0      |
  | 7   | 2      | 2020-02-01 | 100.00          | 150.00 | 0      |
  | 8   | 2      | 2020-02-01 | 250.00          | 0      | 70.00  |
  | 9   | 2      | 2020-02-02 | 180.00          | 120.00 | 0      |
  | 10  | 3      | 2020-02-01 | 100.00          | 200.00 | 0      |
  | 11  | 3      | 2020-02-01 | 300.00          | 0      | 120.00 |
  | 12  | 3      | 2020-02-02 | 180.00          | 130.00 | 0      |
  </code></pre>
  
  <p>My query like below:</p>
  
  <pre><code>SELECT 
      jc.id,
      jc.id_coa,
      jc.date_trx,
      CASE
        WHEN jc.dc = 'D' THEN
        jc.amount
        ELSE 0
      END AS debit,
      CASE
        WHEN dc = 'C' THEN
        jc.amount
        ELSE 0
      END AS credit
    FROM tbl_transaction jc
    WHERE jc.date_trx &gt;= '2020-02-01'
      AND jc.date_trx &lt;= '2020-02-02'
    ORDER BY jc.id_coa ASC
  </code></pre>
  
  <p>My question is:</p>
  
  <p>How to get beginning_balance? </p>
  
  <ul>
  <li><code>beginning_balance = SUM(debit-credit) before '2020-02-01' + debit on date_trx</code> OR</li>
  <li><code>beginning_balance = SUM(debit-credit) before '2020-02-01' - credit on date trx</code></li>
  </ul>