title: Oracle opening and closing balance - SQL or PL/SQL needed?
body: <pre><code> select year, 
          month ,
          d.PROD_ID, 
          T.CUSTOMER_ID,
          SUM(CASE WHEN D.OP_TYPE = 1 THEN d.qty END)    EARNED,
          SUM(CASE WHEN D.OP_TYPE = 2 THEN d.qty END)    SPEND
  FROM TXN_HEADER T  , 
       TXN_DETAIL d , 
       CUSTOMER A,
       PRODUCT e 
  WHERE T.AMOUNT &gt; 0 
  AND   A.TYPE  =  0
  AND   T.CUSTOMER_ID = A.CUSTOMER_ID
  AND   T.TXN_PK = D.TXN_PK 
  and   d.PROD_ID = e.PROD_ID
  and   e.unit = 0
  group by year, month ,d.PROD_ID, T.CUSTOMER_ID
  ORDER BY 1,2,3,4 
  </code></pre>
  
  <p>Output is as follows (here opening and closing not generated by query, but I required that has to be from the query)</p>
  
  <pre><code>YEAR MONTH PROD CUSTOMER OPENING EARNED SPEND CLOSING 
  ---- ----- ---- -------- ------- ------ ----- -------
  2012     8  548    12033       0      8     2       6
  2012     9  509    12033       0     24     0      24
  2012     9  509    12047       0     14     0      14
  2012     9  548    12033       6      1     0       7
  2012     9  548    12047       0      1     0       1
  </code></pre>
  
  <p>I required to generate the output as above. Here PROD_ID,CUSTOMER_ID wise dynamically the prev closing balance to be populated as opening and it shoulde calculate closing balance (opening+earned-spend) monthwise,customer wise ,product wise. is it possible to write in SQL or need to go PL/SQL?</p>