efficient way for sql query to calculate balance

Question

2.00/5 (1 vote)

See more:

what is the fastest/efficient way to solve this?

Table: transaction

ID     user        date             deposit         withdraw
1       A        2014-01-21           100              0
2       A        2014-01-21             0             30
3       A        2014-01-21           300              0
4       A        2014-01-23             0             20
5       B        2014-01-23            20              0

Expected Result: (Filter By user = 'A' AND date <= '2014-01-24')

   Date          B/F         Deposit      Withdraw     Balance
2014-01-21         0           400          -30          370
2014-01-22       370             0            0          370
2014-01-23       370             0          -20          350
2014-01-24       350             0            0          350

Note: Initially, i designed the transaction database by combine the 'deposit' and 'withdraw' into 'amount' and add new field named 'type' by 'D' indicate 'Deposit' and 'W' indicate 'Withdraw'. But i believe it needs more calculation to come out with the result.

Please correct me if i was wrong.
1. Which database design is more efficient?
2. How about the query? should i do the calculation inside the query?(Please provide the sample query if possible) or i read line by line and do calculation at code behind? im using c#, asp.net
_______________UPDATED_________________________________________________
i managed to get the first row, how about the rest?

C#

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    Label lblBF = (Label)e.Row.FindControl("lblBF");
    Label lblDeposit = (Label)e.Row.FindControl("lblDeposit");
    Label lblWithdraw = (Label)e.Row.FindControl("lblWithdraw");            
    Label lblTotal = (Label)e.Row.FindControl("lblTotal");

    decimal d_bf = 0;
    // code to run query
    //"SELECT IFNULL(SUM(deposit- withdraw), 0) Balance FROM transaction WHERE date BETWEEN '2013-01-01' AND '2013-12-31'");
    if (dataReader.Read())
    {
        d_bf = Convert.ToDecimal(dr["balance"].ToString());
    }
    ConnectionClose();
        
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        if (e.Row.RowIndex == 0) //calculation for 1st row
        {
            lblBF.Text = d_bf.ToString();
            lblTotal.Text = Convert.ToString(Convert.ToDecimal(lblBF.Text) + Convert.ToDecimal(lblDeposit.Text) - Convert.ToDecimal(lblWithdraw.Text));
        }

        else //calculation for the rest
        {
            // how to get prev row/cell
        }
                        
        if (lblWithdraw.Text != "0.00")
        {
            lblWithdraw.Text = "- " + lblWithdraw.Text;
        }
    }
}

Posted 7-Apr-14 3:59am

melvintcs

Updated 13-Apr-14 4:31am

v3

Add a Solution

Comments

Suk@nta 7-Apr-14 10:38am

their is no column B/F you mention. can you write where you got

melvintcs 7-Apr-14 10:48am

sorry, my mistake. B/F means "Brings Forward", the balance of the previous day brings to today. 21 Jan is '0' because no have deposit stored yet. 22 Jan is '370' because 100+300-30 on 21 Jan.

Suk@nta 7-Apr-14 15:13pm

check the solution 2

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2014-04-07T05:40:00

Solution 1

I think you're thinking about this the wrong way. The B/F column doesn't have anything to do with the calculation. The current balance is the sum of all of the Deposits and, with the data you're showing, the sum of all the Withdrawls added together. If the amounts in the Withdrawls column are position values, then you would subtract it from Deposits.

SELECT (SUM(Deposit) + SUM(Withdrawl)) As Balance FROM whateverTable

The running Balance as you're displaying is a UI thing, not calculated by the database. There, you would have to do the calculation, row by row, in your C# code.

Posted 7-Apr-14 5:40am

Dave Kreskowiak

Updated 7-Apr-14 5:44am

v2

Comments

Sergey Alexandrovich Kryukov 7-Apr-14 14:44pm

Agree, a 5.
—SA

melvintcs 13-Apr-14 10:32am

please see my updated question :)

[no name] 13-Apr-14 11:32am

No you need to ask a different question instead of changing your existing question adding on completely separate questions. And you should be prepared to answer the question "why can't you write a loop to loop through the records?".

melvintcs 13-Apr-14 11:47am

i believe even i updated my question. it's still in the topic of "efficient way for sql query to calculate balance". i did the for loop function; my code for i==0 is same with e.Row.RowIndex == 0 as above, but for the second record onward, they detect the cell[4] as NULL :)

[no name] 13-Apr-14 17:17pm

How do you figure that? The two questions have nothing at all to do with each other. One question is calculation and the other is looping? How exactly do you figure that they are in any way related?

Suk@nta · Answer 2 · 2014-04-07T09:12:00

SQL

// i have tested above table and find a solution , try below query it works fine

WITH CTE AS (
SELECT  
rownum = ROW_NUMBER() OVER (ORDER BY D.Date),D.Date as Date,D.BF as     BF,D.Deposite,D.Withdraw,D.balance
  FROM (
  select date as Date ,0 as BF,sum(deposite) as Deposite,-1*sum(withdraw) as Withdraw, SUM(deposite)-SUM(withdraw) as balance from testTable where Username='A' and date<=CONVERT(SMALLDATETIME,'24/01/2014',103)   group by date)D)

   select Date, ISNULL(BF,0) as 'B/F',Deposite,Withdraw,ISNULL(BF,0)+Deposite+Withdraw as Balance
   from 
   (
    select Date ,ISNULL(BF,0)+(select balance from  CTE prev where prev.rownum = CTE.rownum - 1) as BF ,Deposite,Withdraw,balance as Balance from CTE 
    )x

//don't forgot to accept the answer if you find it helps