Click here to Skip to main content
15,665,229 members
Please Sign up or sign in to vote.
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
Updated 13-Apr-14 4:31am
v3
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

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.
 
Share this answer
 
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?
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
 
Share this answer
 
Comments
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)



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