15,937,602 members
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'");
{
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
Updated 13-Apr-14 4:31am
v3
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

## 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.

v2
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?

## Solution 2

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

Dave Kreskowiak 7-Apr-14 15:34pm
It may work but I have to question your solutions efficiency. How does this perform over a set of a million records? I'd be very curious to see the execution plan on this.
Suk@nta 7-Apr-14 16:43pm
i don't know whether it is efficient one or not
melvintcs 7-Apr-14 21:06pm
can "WITH" syxtax used on mysql? im getting error with "WITH"
Suk@nta 8-Apr-14 4:49am
sorry 'WITH' keyword doesn't support in mysql. this feature has been requested since 2006. except this other database server accept

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

Top Experts
Last 24hrsThis month
 Pete O'Hanlon 75 OriginalGriff 10 merano99 10 Richard MacCutchan 5 Ganesh Jeevaa 5
 OriginalGriff 730 Pete O'Hanlon 675 Richard Deeming 475 merano99 225 Dave Kreskowiak 180

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900