List:General Discussion« Previous MessageNext Message »
From:Barry Newton Date:August 4 2006 11:02pm
Subject:Re: Running Totals?
View as plain text  
Well, I said earlier that if I found a solution to this, I'd post it.  Here 
it is, with many thanks to Nicholas Bernstein's timely July 7 post to the 
doc on user variables:

It's not particularly elegant, it just gets the job done.   If there is a 
cleaner way to do this, I'm not ashamed to be educated.

Barry



*  Compquery.sql -- Compare Current Year Reg Numbers and Money to Prior Year */
/* 
*/
/* 
*/


/* ACCUMULATE DATA BY MONTH FOR BOTH 
YEARS                                        */

Drop Table If Exists Montable, Montable2;

Create Temporary Table Montable engine=memory
Select  Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as 
Registrations,
Extract(Year_Month from DatePaid) Monindex,
Sum(Amount) as Paid
 From capclave2005reg
where ( amount > 0)
Group by Monindex;

Create Temporary Table Montable2 engine=memory
Select  Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as 
Registrations,
Extract(Year_Month from DatePaid) Monindex,
Sum(Amount) as Paid
 From Capclavepresent
where ( amount > 0)
Group by Monindex;


/*  REPORT FOR BOTH YEARS WITH RUNNING 
TOTALS                                    */

Set @cumreg=0, @cumreg2=0, @cumpd=0, @cumpd2=0;

Select   Month,  Year,  Registrations, Paid RegIncom,
  Monindex, @cumreg:=@cumreg + Registrations  RegYearToDate, @cumpd:= 
@cumpd+Paid RegIncomeYTD
 From Montable

Union

Select   Month,  Year,  Registrations, Paid RegIncome,
  Monindex, @cumreg2:=@cumreg2 + Registrations  RegYearToDate, @cumpd2:= 
@cumpd2+Paid RegIncomeYTD
 From Montable2  ;



Barry Newton


Thread
Running Totals?Barry Newton3 Aug
  • Re: Running Totals?Peter Brawley3 Aug
    • Re: Running Totals?Barry Newton3 Aug
      • Re: Running Totals?Barry Newton5 Aug
  • Re: Running Totals?Brent Baisley3 Aug
Re: Running Totals?Barry Newton4 Aug