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:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd:=
@cumpd+Paid RegIncomeYTD
From Montable
Union
Select Month, Year, Registrations, Paid RegIncome,
Monindex, @cumreg2:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd2:=
@cumpd2+Paid RegIncomeYTD
From Montable2 ;
Barry Newton
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
