CODEWITHSUNDEEP
sqlsql-server
navnit
navnit

Reputation: 310

sql server running total with condition with debit credit balance

i want to do running total of amount based on debit and credit amt in following table there is one column drcr in which 'c' stand for credit and 'd' for debit and it should be done with grouping name also.

i want following output using sql server query

    Name  amt drcr Total
    | 'abc'| 1| 'c'|1 |
    | 'abc'| 5| 'd'|-4|
    | 'abc'| 2| 'c'|-2|
    | 'abc'| 1| 'c'|-1|
    | 'bcd'| 1| 'c'|15|
    | 'bcd'| 1| 'd'|0 |
    | 'bcd'| 5| 'c'|5 |
    | 'bcd'| 8| 'd'|-3|
    | 'bcd'| 0| 'c'|-3|

from following input 

CREATE TABLE #temp
(
  intId INT IDENTITY(1, 1) ,
  varName VARCHAR(50) ,
  decAmt DECIMAL(28, 0) ,
  charCrDr CHAR(1) ,  -- c cr, d dr
  decTotal DECIMAL(28, 0)
)
INSERT  INTO #temp
    ( varName, decAmt, charCrDr )
VALUES  ( 'abc', 1, 'c' ),
    ( 'abc', 5, 'd' ),
    ( 'abc', 2, 'c' ),
    ( 'abc', 1, 'c' ),
    ( 'bcd', 15, 'c' ),
    ( 'bcd', 15, 'd' ),
    ( 'bcd', 5, 'c' ),
    ( 'bcd', 8, 'd' ),
    ( 'bcd', 0, 'c' )

i have solved it using following solution 

SELECT  *
INTO    #temp1
FROM    #temp AS T

UPDATE  #temp1
SET     #temp1.intAmt = CAST(-#temp1.decAmt AS DECIMAL)
WHERE   charCrDr = 'd'

SELECT  T.intId ,
    T.varName ,
    T.charCrDr ,
    T.decAmt ,
    SUM(T2.decAmt) AS balance
FROM    #temp1 AS T
    INNER JOIN #temp1 AS T2 ON T2.varName = T.varName
                               AND T2.intId <= T.intId
GROUP BY T.intId ,
    T.varName ,
    T.charCrDr ,
    T.decAmt

but i want to what is best way to do it

thank u

Upvotes: 2

Views: 4477

Answers (3)

Manish
Manish

Reputation: 1

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*"%>
<%@ page import="java.util.Calendar" %>
<%
//java Code
String date = (new java.util.Date()).toString();
String UserName = request.getParameter("UserName");
String CusId= request.getParameter("CusId");
String AccountNo = request.getParameter("AccountNo");
String Debit = request.getParameter("Debit");
String Credit=request.getParameter("Credit");
String Balance=request.getParameter("Balance");
String sDate=request.getParameter("sDate");
try
{
String s="jdbc:odbc:Database1";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=DriverManager.getConnection(s);
Statement smt=conn.createStatement();
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
String sql= "SELECT  sDate, (SUM(Debit)*-1) + SUM(Credit)Balance FROM abcbank GROUP BY  sDate";
ResultSet result = smt.executeQuery(sql);
int count = 0;
while (result.next())
{
result.getString(1,"Debit");
result.getString(2,"Credit");
result.getString(3,"Balance");
result.getDate(4,startDate);

}

String update= "UPDATE abcbank SET Balance =Debit + Balance   WHERE AccountNo="+AccountNo+" ";
PreparedStatement statement = conn.prepareStatement(update);
//statement.setString(1,"AccountNo");
int rowsUpdated = statement.executeUpdate();
if (rowsUpdated ==0)
{
out.println("This Emp does not Exists!");
}
else if(rowsUpdated ==1)
{
out.println("An existing user was updated successfully!");
}
conn.close();
}
catch(Exception ex)
{
System.err.println(ex.getMessage());
  }

%>
</body>
</html>

Upvotes: 0

Arun Gairola
Arun Gairola

Reputation: 884

HI Navnit This is simple you use 'Sum( case when charCrDr = 'c' Then decAmt else -decAmt end) over( order by intId)` in your statement . 

select *,Sum( case when charCrDr = 'c' Then decAmt else -1*decAmt end) over( PARTITION BY varName ORDER BY intId) as Total
     from #temp t

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

Try this: (Improved)

SELECT intId, varName, decAmt, charCrDr, 
       SUM(CASE WHEN charCrDr = 'c' THEN
               decAmt
           ELSE
              decAmt * -1
           END )
           OVER (PARTITION BY varName ORDER BY intId) As decTotal
FROM #Temp;

see fiddle here

Explanations:

  • The CASE will decide if to add the decAmt as a positive or negative number to the SUM.
  • The PARTITION BY means that the sum will be calculated for each varName individually (that was missing in the other answer)
  • the ORDER BY will ensure that the sum will be calculated correctly

This will work on any supported Sql Server version (2005 and above).

Upvotes: 3

Related Questions