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.

Tags:
  1. nikhil9421

    nikhil9421 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    100
    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

    Messages:
    735
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hello!
    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
    )

    SELECT
      yd.*,
      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