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!

How to calculate debit-credit-balance-sdate for sql query

Discussion in 'SQL PL/SQL' started by Kumarmanish, Sep 20, 2016.

  1. Kumarmanish

    Kumarmanish Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Varanasi
    ID-AccountNo-Custname-Debit-Credit-Balance-sDate
    1-19898-Manish-500-0-500-19/09/2016
    2-1999-kumar-700-0-700-19/09/2016
    3-19898-Manish-0-500-200-21/09/2016
    4-1999-kumar-500-0-1200-19/09/2016
    Sir, i have query but no response plz suggest us how to implements those or suggest new SQl query.
    String sql ="SELECT Debit, Credit, sDate SUM(Debit-Credit) OVER (ORDER BY sDate) Balance FROM Customer " ;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    simple by example:
    Code (SQL):
    WITH your_data(id,accountno,custname,debit,credit,balance,sdate) AS
    (
    SELECT 1,19898,'Manish',500,0,500,to_date('19/09/2016','dd/mm/yyyy') FROM dual UNION ALL
    SELECT 2,1999,'kumar',700,0,700,to_date('19/09/2016','dd/mm/yyyy') FROM dual UNION ALL
    SELECT 3,19898,'Manish',0,500,200,to_date('21/09/2016','dd/mm/yyyy') FROM dual UNION ALL
    SELECT 4,1999,'kumar',500,0,1200,to_date('19/09/2016','dd/mm/yyyy') FROM dual
    )

    SELECT
        yd.*,
        SUM(debit-credit) OVER (ORDER BY sdate ROWS unbounded preceding) new_balance
    FROM your_data yd

     
     
  3. Kumarmanish

    Kumarmanish Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Varanasi
     
  4. Kumarmanish

    Kumarmanish Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Varanasi
    =================
    thank you very much to suggest us but i am learner of SQl as well jsp page,i am little bit confuse of which suggest us ,even if possible plz describe like my Table is name "Customer"and Column-ID-AccountNo-Debit-Credit-Balance-sDate and i am trying to fill entire process via JSP form Page [AccountNo,Name,Amount] both for deposit and withdraw plz sugget us how to mention quey over the JSP Page FORM
    Example
    try
    {
    String s="jdbc:eek:dbc:Database1";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection conn=DriverManager.getConnection(s);
    Statement st = conn.createStatement();

    [
    SELECT t.[InvoiceID], t.S_Type as Type,
    t.Date, t.Debit, t.Credit, b.Balance
    FROM Statement as t CROSSapply(SELECT Balance = SUM(Debit)- SUM(Credit)FROM Statement as xWHERE(x.date < t.date or
    x.date = t.date
    )AND
    x.CustID = t.CustID
    ) bWHERE t.CustID ='48'and date between'2015-01-01'and'2016-01-01'ORDERBY t.date
    ]
    ResultSet rs=st.executeQuery(text);
    while(rs.next())
    {
    rs.getString(1);
    }
    rs.close();
    conn.close();
    }
    catch(Exception ex)
    {
    System.err.println(ex.getMessage());
    }

    Please review above content and suggest us how to write over the JSP Page in which One Page is Deposit -transfer to message your balance is somthing990990 like that same with withdraw page.

    plz review and help me go further.
    Manish Kumar[Learner]
     
  5. Kumarmanish

    Kumarmanish Newly Initiated

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Varanasi