MySQL Forums
Forum List  »  Newbie

running balance with where
Posted by: Amos Marzuoli
Date: December 28, 2007 11:59AM

Good morning,

I am using the following query:
SET @bal1=0,@bal2=0,@bal3=0,@bal4=0,@bal5=0,@bal6=0;
SELECT Id, Date, FCode, Descr, Debit, @bal5 := @bal5 + DebT AS RunDeb, Credit, @bal6 := @bal6 + CreT AS RunCre, @bal2 := @bal2 + net AS RunBal, Rec
FROM (
  select
    fin_records.id,
	fin_records.id_production_unit as PUnit,
	fin_records.id_bank_account as BAcc,
	date_format(fin_records.fin_record_date,'%d-%m-%y') as Date,
	fin_accounts.fin_account_code as FCode,
	fin_records.fin_record_desc AS Descr,
	fin_records.fin_record_debit AS Debit,
	@bal3 := fin_records.fin_record_debit AS DebT,
    fin_records.fin_record_credit AS Credit,
	@bal4 := fin_records.fin_record_credit AS CreT,
    @bal1 := fin_records.fin_record_debit - fin_records.fin_record_credit AS Net,
	fin_records.fin_record_rec AS Rec
    from fin_records inner join fin_accounts on fin_accounts.id=fin_records.id_fin_account where (fin_records.id_production_unit='$up') and (fin_records.id_bank_account='$ba') and (fin_record_date between makedate(year(curdate()),1) and makedate(year(curdate()),365)) order by fin_records.fin_record_date,fin_records.id
) AS tmp;

I should build another column to get RunBal2, that should include only records with Rec = 1.

Is there a system to get such result?

Thank you for your attention.
Amos

Options: ReplyQuote


Subject
Written By
Posted
running balance with where
December 28, 2007 11:59AM
December 28, 2007 02:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.