how to calculate the value of one row to another

[kutta_vin]

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?

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

[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

[kutta_vin]

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.

[kutta_vin]

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.

[tesuji]

Hi kutta_vin

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

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!

[kutta_vin]

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

[tesuji]

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?

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

[kutta_vin]

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

[master_m]

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?

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

The 'balance' column returns nothing. Whats wrong with the code?

[kselva]

Hello Tesu,

This is really helpful for my requirement. I am using the query

select id, mill_rate, mill_rate+coalesce((select sum(mill_rate) 
  from mdata b where b.id < a.id ),0) as sumMill
 from mdata a 
where a.fiscalyear=2008
and  a.plant_number='xx'
 ORDER BY a.id;

but it sum the entire mdata mill_rate column but I want to sum only based on fiscalyear=2008 and plantnumber=xx. i tried like this in the subquery to include fiscalyear but it is not allowing. I am using oracle.

select id, mill_rate, mill_rate+coalesce((select sum(mill_rate) 
  from mdata b where b.id < a.id ),0 and b.fiscalyear='2008') as sumMill
 from mdata a 
 where a.fiscalyear=2008
and  a.plant_number='xx'
ORDER BY a.id;

Please advise.

Regards
Selva

[kselva]

Hello Tesu,

I found the answer. i have used alias name in the subquery.

Thank You.

Regards
Selva

[Mukoro]

The post saved me and thanks to every I was able to an account ledger