how to calculate the value of one row to another

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 402,602 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,261 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1106 | Replies: 7 | Solved

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

how to calculate the value of one row to another

Jun 4th, 2008

Hello friends,

I am new to this site, this is my first post, My question is..

How to calculate the one rows value for next row value?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT @rownum:=@rownum+1 "sl.no",
 
       						b.bus_activity_desc "Type",r.trn_no,
 							o.Office_name,o.Address,o.Address2,o.Address3,
            				DATE_FORMAT(r.trn_date,GET_FORMAT(DATE,'EUR')) AS trn_date,
            				l.Account_id ,
				          IF(r.trn_type=6,r.total_trn_value,0) "Credit",
                  IF(r.trn_type=7,r.total_trn_value,0) "Debit",
                  IF(r.trn_type=6 ,@cbal:=(r.total_trn_value+@cbal),@cbal:=(@cbal-r.total_trn_value)) "Closing Balance"
 
 
 
					FROM(SELECT @rownum:=0) s,
						office_master o,
						center_group_customer_master ce,
     					receipt_payment_header r,
     					saving_accounts l,
     					(SELECT @cbal:=0) c ,
     					accounts_summary_details a,
     					business_activity_master b
					WHERE a.Account_id=l.savings_id AND
						  a.Customer_id=l.Customer_id AND
						  a.Product_id=2 AND
						  r.Trn_type IN (6,7) AND
						  r.Customer_id=l.Customer_id AND
						  r.Account_id=l.savings_id AND
						  b.Bus_activity_code=r.Trn_type AND
						  l.account_id='23' AND
						  r.trn_date BETWEEN '2006-06-01' AND '2008-06-31' AND
						  o.Office_id=ce.office_id AND
						  ce.customer_id=r.customer_id ORDER BY r.trn_no;
select @rownum:=@rownum+1 "sl.no",

       						b.bus_activity_desc "Type",r.trn_no,
 							o.Office_name,o.Address,o.Address2,o.Address3,
            				DATE_FORMAT(r.trn_date,GET_FORMAT(DATE,'EUR')) as trn_date,
            				l.Account_id ,
				          if(r.trn_type=6,r.total_trn_value,0) "Credit",
                  if(r.trn_type=7,r.total_trn_value,0) "Debit",
                  if(r.trn_type=6 ,@cbal:=(r.total_trn_value+@cbal),@cbal:=(@cbal-r.total_trn_value)) "Closing Balance"



					from(select @rownum:=0) s,
						office_master o,
						center_group_customer_master ce,
     					receipt_payment_header r,
     					saving_accounts l,
     					(select @cbal:=0) c ,
     					accounts_summary_details a,
     					business_activity_master b
					where a.Account_id=l.savings_id and
						  a.Customer_id=l.Customer_id and
						  a.Product_id=2 and
						  r.Trn_type in (6,7) and
						  r.Customer_id=l.Customer_id and
						  r.Account_id=l.savings_id and
						  b.Bus_activity_code=r.Trn_type and
						  l.account_id='23' and
						  r.trn_date between '2006-06-01' and '2008-06-31' and
						  o.Office_id=ce.office_id and
						  ce.customer_id=r.customer_id order by r.trn_no;

The above given is my query, don't search any mistakes in that, I am just asking the logic to calculate "closing balance" according to r.trn_no, please help me

Last edited by peter_budo : Jun 14th, 2008 at 1:29 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: how to calculate the value of one row to another

Jun 4th, 2008

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

Re: how to calculate the value of one row to another

Jun 5th, 2008

•

Originally Posted by tesuji

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu

Hai, Let me give some sample , for example..

I need the Mysql query to find the Balance calculations from credit and debit amount
of below noted table.But in the ACT table there is no field Balance column .
we must create and calculate the balance field at the run time.So,give me the solutions .

The output must looks like this.(i.e) for two values the output comes correctly like the following.
If credit means the balance must be added and if debit means the we must be subtract the debit
amount from balance.

Table Name : Account Transaction Ledger(ACT)
Table Fields : Transaction Id,Credit,Debit

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00| 200.00

----------------------------------------------------------------------------------

But if we got the another credit and debit in that table, then the problem arise, that is for example

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 200.00
DB10006 | 40.00 | 0.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

As above given, i must get the result but i am not getting like that, instead of that all the credit amounts
first added and then the debit amounts are subtracted.(i.e) the actual result coming for me is

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10006 | 40.00 | 0.00 | 273.00
DB10005 | 0.00 | 33.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

Even though I gave order by transactionId the result is coming like the following.
------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 273.00
DB10006 | 40.00 | 0.00 | 240.00
DB10006 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

I must get the result as the second table, so please help me to find the solution.

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

Re: how to calculate the value of one row to another

Jun 5th, 2008

•

Originally Posted by tesuji

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu

hello tesuji please help me.

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: how to calculate the value of one row to another

Jun 5th, 2008

Hi kutta_vin

this is really a hard task. Finally I have got the following solution:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) 
  FROM act b WHERE b.transaction < a.transaction),0) AS sumCredit, 
    debit+coalesce((SELECT sum(debit) FROM act b 
      WHERE b.transaction < a.transaction),0) AS sumDebit,  
        sumCredit - sumDebit  AS Balance  FROM act a ORDER BY transaction;
 
/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/
select transaction, credit, debit, credit+coalesce((select sum(credit) 
  from act b where b.transaction < a.transaction),0) as sumCredit, 
    debit+coalesce((select sum(debit) from act b 
      where b.transaction < a.transaction),0) as sumDebit,  
        sumCredit - sumDebit  as Balance  from act a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

It is important that the rows be uniquely sequenced by Transaction column.

This is a modification of a well-known Celko-solution for running totals I have rewritten to meet your requirements. However, I haven't tested it completely, yet it seems that this solution meets at least your small sample. Tell me whether above solution is right to solve your problem.

krs,
tesu

p.s. Oh sorry, I really hope that MySQL would also be able to deal with subselects as specified in SQL standard 1999!

Last edited by tesuji : Jun 5th, 2008 at 8:10 pm.

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

Re: how to calculate the value of one row to another

Jun 6th, 2008

Hai tesuji,

Thank U for your reply. sorry i have missed to mention one thing.....
that is ,
In My ACT table credit and debit fields are not a field name . it is a alias name of total_transaction_value according to the transaction type that is deposit or withdrawal i have calculate the debit and credit . that is if the transaction_type is DEPOSIT then the total_transaction_value(Credit alias for total_transaction_value ) is fetched from ACT table thats alias name is CREDIT. If the transaction_type is WITHDRAWAL then the total_transaction_value(Debit Alias for total_transaction_value) is fetched from ACT table thats alias name is DEBIT .

Actually my table(ACT) is look like this .,

Table Column name : AccountId
TransactionID
Transaction_type
Total_transaction_value

The Actual table is looks like this,
-------------------------------------------------------------------------------------------------
Account_id | TransactionId | Transaction_type | total_transaction_value
-------------------------------------------------------------------------------------------------
SA001 | DB10004 | Deposit | 233.00
SA001 | DB10005 | Withdrawal | 33.00
SA001 | DB10006 | Deposit | 40.00
SA001 | DB10007 | Withdrawal | 20.00
-------------------------------------------------------------------------------------------------

TransactionId is unique for each transactions(deposit/withdrawal).

From this table columns name we have to show the output like what i told before.

that is .,

-------------------------------------------------------------------------------
TransactionID | CR | DR | BALANCE
-------------------------------------------------------------------------------
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 200.00
DB10006 | 40.00 | 0.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00

here the above table(ACT) ., CR and DR is only the alias name of total_transaction_value.
it is seperated only by of transaction_type.

Kindly give me the solutions for this requirement.

regards,

kutta_vin

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: how to calculate the value of one row to another

Jun 6th, 2008

Hello kutta_vin,

well, "forgotten to mention" has created some superfluous work. However it is rather simple to get the same result from your modified tables. Supposing a table act2(AccountId, TransactionID, trtype, trvalue) exists, then there are some possibilities to solve your problem. All solutions that come into consideration are based on new CASE clause (MySql supports it). Then, one can solve the problem by means of 1) stored procedures, 2) SQL views, or 3) WITH clause (MySql 5 has it).

I have used the WITH clause as you can see. With WITH clause first a temporary table tact which selects from your new table structure act2 the old structure with columns transaction, credit and debit. On that temporary table my yesterday posted SQL select can be executed without any changes. Nice, isn't it?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
WITH tact (transaction, credit, debit) AS
(
   SELECT TransactionID, 
     case trtype
          when 'Deposit' then coalesce(trvalue, 0)
          else 0
     end AS Credit,
     case trtype
          when 'Withdrawal' then coalesce(trvalue, 0)
          else 0
     end AS Debit
   FROM act2 WHERE accountID = 'SA001'
) 
SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) FROM tact b
  WHERE b.transaction < a.transaction), 0) AS sumCredit, debit+coalesce((SELECT sum(debit) FROM tact b
    WHERE b.transaction < a.transaction), 0) AS sumDebit, sumCredit - sumDebit  AS Balance
       FROM tact a ORDER BY transaction;
 
/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/
with tact (transaction, credit, debit) as
(
   select TransactionID, 
     case trtype
          when 'Deposit' then coalesce(trvalue, 0)
          else 0
     end as Credit,
     case trtype
          when 'Withdrawal' then coalesce(trvalue, 0)
          else 0
     end as Debit
   from act2 where accountID = 'SA001'
) 
select transaction, credit, debit, credit+coalesce((select sum(credit) from tact b
  where b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((select sum(debit) from tact b
    where b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       from tact a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

I hope this code helps you.

krs,
tesu

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

Re: how to calculate the value of one row to another

Jun 17th, 2008

Hi tesuji,

the problem solved for me with your old query idea, that is with the help of

(SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit)

FROM act b WHERE b.transaction < a.transaction),0) AS sumCredit,

debit+coalesce((SELECT sum(debit) FROM act b

WHERE b.transaction < a.transaction),0) AS sumDebit,

sumCredit - sumDebit AS Balance FROM act a ORDER BY transaction; )

this query. But the alias names sumCredit - sumDebit cannot be evaluated with only alias names , i used the same condition for that ,( credit+coalesce((SELECT sum(credit)
FROM act b WHERE b.transaction < a.transaction),0))-(debit+coalesce((SELECT sum(debit) FROM act bWHERE b.transaction < a.transaction),0)).