John,

>I think the main problem is that it is not really a function - more set manipulation.
>Just for fun (I know I need to get a life!) I have made an attempt at a generalised solution in a Stored Proc

Thanks for the example. I tried it, made some minor mods and have it working.
I am in the process of implementing it in Delphi where I can work on it better. I may port it back to a stored procedure, or keep it in Delphi, I'm not sure at this point.

In my case, I'll have the code create a temporary table with some (or all of the original table columns) along with the SMA's and use this table for my readonly grids and reports. The other alternative was to create a temporary table with only the SMA columns and join it to the original table using EDBQuery. Either way, it will give me the SMA's for grids and reports that I need.

Thanks again for elegant solution.

Barry

Barry,

<< Of course the problem with any moving average (or moving 'anything') is
you have to store the last 'n' values in an array and replace the oldest
value with the newest value to get the new moving average. So I'm not sure
if storing an array is even possible in an external module. (I've never
written an external module before.) >>

Have you considered using a stored procedure to do these calculations
instead ?  If you don't need a sensitive result set, you could handle all of
the temporary table creation/population in the stored procedure and then
return the a simple 'TABLE <TemporaryTableName>' cursor (use WITH RETURN in
the DECLARE for the CURSOR).  Temporary tables are automatically separated
by session boundaries, so you could just use a specific name for such tables
and they won't conflict with other temporary tables, etc. and will
automatically be removed by EDB when the session ends.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Barry,

<< Of course the problem with any moving average (or moving 'anything') is
you have to store the last 'n' values in an array and replace the oldest
value with the newest value to get the new moving average. So I'm not sure
if storing an array is even possible in an external module. (I've never
written an external module before.) >>

<Have you considered using a stored procedure to do these calculations
instead ?  If you don't need a sensitive result set, you could handle all of
the temporary table creation/population in the stored procedure and then
return the a simple 'TABLE <TemporaryTableName>' cursor (use WITH RETURN in
the DECLARE for the CURSOR).  Temporary tables are automatically separated
by session boundaries, so you could just use a specific name for such tables
and they won't conflict with other temporary tables, etc. and will
automatically be removed by EDB when the session ends.>

Tim,

I was under the impression declaring a cursor WITH RETURN was useful only if the SP was called from another SP.  Or can I do something weird and wonderful like:

   select * from MyStoredProcedure(parm1,parm2);

provided MyStoredProcedure defines the cursor WITH RETURN?

The ultimate use of the temporary Avg_Table would be to join it with the original table (or include the original table cols in the Avg_Table) and use this in a Delphi grid or report. (non-Sensitive)

Yes I can summarize the table and build the SMA (simple moving averages) in a stored procedure, and pass back the name of the temporary table to Delphi, but what is the advantage of using a SP compared to doing it all in Delphi? A SP would save a little on bandwidth but is there any other advantage to using a SP over a Delphi procedure?

TIA
Barry