Re: SQL for running average calculations

On Sun, 9 Mar 2008 23:38:37 -0500, "DFS" <nospam@xxxxxxxx> wrote:

MLH wrote:
On Sun, 9 Mar 2008 18:52:42 -0500, "DFS" <nospam@xxxxxxxx> wrote:

SELECT SalesPeriod, RunningTotal([SalesPeriod]) as CumulativeSales
FROM Table;

Public Function RunningTotal(SalesPeriod As Date) As Currency
RunningTotal = DSum("SalesAmt", "Table", "SalesPeriod <= #" &
SalesPeriod & "#")
End Function


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I see. You're calling FN that does the work. I was hoping to
do it entirely in the SQL, w/o calling a FN.

You probably can, and I'd say it's preferable to contain it in the SQL. I
tried a SQL statement and my first one didn't work so I wrote the function,
and it ran first time.

Looking at your other post, since your SQL includes a GROUP BY on the Year,
you'll always get a row per year, at least.


I have found that
FN's are great for making short work of what can't be figured
out inside SQL. But the FN calls seem to really slow down
the formation of the dynaset. They seem to place a real burden
on the process. Maybe I'm wrong. But its the impression I've
gotten over the years.

Speed of the function depends on the function code, your datasets, indexing,
and hardware of course. You said you're drawing graphs off the summary, so
you don't need a dynaset - in fact your query won't be updateable anyway
(and my example will be only if you don't do any groupings).

How much speed do you need? Are the graphs updated in real time as a user
scrolls a data***? Do you have indexes on your date fields? Are your
datasets large?
Not much. No. No. No. So really, a procedure call isn't detrimental.
But - I've been working on it and I finally got it...


OK. I got it. Here it is...
SELECT Sum(tblPmtsRcd.PmtAmt) AS SumOfPmtAmt, Year([PmtRecTDStamp]) &
IIf(Month([PmtRecTDStamp])<10,"0" &
Month([PmtRecTDStamp]),Month([PmtRecTDStamp])) AS YrMo,
DSum("PmtAmt","tblPmtsRcd","Year([PmtRecTDStamp]) &
IIf(Month([PmtRecTDStamp])<10,'0' &
Month([PmtRecTDStamp]),Month([PmtRecTDStamp]))<=" & [YrMo]) AS RSum,
Max(tblPmtsRcd.PmtRecTDStamp) AS MaxOfPmtRecTDStamp,
Min(DMin("[PmtRecTDStamp]","tblPmtsRcd")) AS MinDate,
DateDiff("m",[MinDate],[MaxOfPmtRecTDStamp])+1 AS Months,
Format([RSum]/[Months],"$0") AS RAvg
FROM tblPmtsRcd
GROUP BY Year([PmtRecTDStamp]) & IIf(Month([PmtRecTDStamp])<10,"0" &
Month([PmtRecTDStamp]),Month([PmtRecTDStamp]))
ORDER BY Year([PmtRecTDStamp]) & IIf(Month([PmtRecTDStamp])<10,"0" &
Month([PmtRecTDStamp]),Month([PmtRecTDStamp]));

I had to group by Year (string) concatted with Month (2-digit string)
a sort by the same thing: 200606, 200607, 200608 .... 200801, 200802.
Wanted the dynaset to show total monthly sales each month, gross sales
to date each month and average monthly sales each month. The latter
two are a running sum and a running average respectively. The first
dataset graphed each month as a vertical barchart clearly portrays
sales growth. The second as a line graph clearly demonstrates the
growing total of gross receipts over time and the last demonstrates
stability, for instance, if lifetime average monthly sales receipts
are on the rise.
.

Loading