1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help with sql / plsql

Discussion in 'SQL PL/SQL' started by nikhil9421, Oct 30, 2017.

  1. nikhil9421

    nikhil9421 Active Member

    Likes Received:
    Trophy Points:
    Hello All,

    I have below kind of data

    Transaction | Debit | Credit | Begening Balance | Ending balance
    INV1 | 100 | | 300 | 400
    INV2 | | 200 | |
    INV3 | 130 | | |
    INV4 | | 150 | |

    This is above data , and we need to calculate begening balance and ending balance.
    Begening balance of INV2 will be ending balance of INV1, and begening balance of INV3
    will be ending balance of INV2, … goes on..
    And ending balance will be calculated as begening balance + debit_amount or begening_balance - credit_amount. but how can i put ending balance of current row to begening_balance of next row.
    Note - First row of begening balance and ending balance will come through gl_balances itself, but need to calculated values from 2nd row.
    Last edited: Oct 30, 2017
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Likes Received:
    Trophy Points:
    Russian Federation
    Attention! Always, when creating a question, provide scripts for creating tables for your task, and scripts for filling tables with data.

    Nikhil, avaible of three methods for solution your task:
    1) using of analitycs function
    Code (Text):

    with your_data (transaction , debit , credit , begening ) AS
    (SELECT 'INV1', 100 , 300, 400 FROM dual UNION ALL
    SELECT 'INV2', to_number(NULL), 200 , to_number(NULL) FROM  dual UNION ALL
    SELECT 'INV3',to_number(NULL), 130 ,to_number(NULL) FROM  dual UNION ALL
    SELECT 'INV4',to_number(NULL), 150 ,to_number(NULL) FROM  dual

      sum(NVL(yd.debit,0)) OVER(ORDER BY yd.transaction ROWS UNBOUNDED PRECEDING) +
      sum(NVL(yd.begening,0)) OVER(ORDER BY yd.transaction ROWS UNBOUNDED PRECEDING)-
      sum(NVL(yd.credit,0)) OVER(ORDER BY yd.transaction ROWS UNBOUNDED PRECEDING) end_balance
    FROM your_data yd;

    2) using of sql&collection
    3) using of modeling -- in basic... for education