Eric
Pending your reply,
you could try
1) UNION QUERY
SELECT whatever, SUM(SomeProp) AS rTotal FROM Class WHERE (conditions
here for say weekly total) GROUP BY xx ORDER BY ...
UNION
SELECT whatever, SUM(SomeProp) AS rTotal FROM Class WHERE (conditions
here for say monthly total) GROUP BY xx ORDER BY ...
UNION
SELECT whatever, SUM(SomeProp) AS rTotal FROM Class WHERE (conditions
here for say yearly total) GROUP BY xx ORDER BY ...
2) The above may not be an ideal solution, as the results (totals) will be
displayed vertically for each employee. The solution may lie in writing a
custom COS query (Query type :%Query) as opposed %SQLQuery
Check-out
http://127.0.0.1:1972/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_storedprocedu
re
You would need to write three methods (to for query named
qryContractorWorkHistory as
qryContractorWorkHistoryExecute()
qryContractorWorkHistoryFetch()
qryContractorWorkHistoryClose()
Check-out
http://personal.vsnl.com/sukesh_hoogan/ccos.html#Nbr04
Hope this helps.
Regards
--
Sukesh Hoogan
e-Linear Enterprise Solutions
Bombay, India
http//personal.vsnl.com/sukesh_hoogan (updated : June12 , 2004)
Eric Gibson <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Does anyone have any ideas on how to implement an sql stored procedure
> aggregate function as a class method? In particular, I need a function
> similar to the SQL SUM function, but instead of each row containing a
> sum of all rows selected by the query, I need a running total of all
> rows up to and including the current row. For instance, I may want to
> list the number of hours worked by an employee for 2003, for each month,
> including a running total of all months up to the current month.
>
> I have tried the following:
>
> ClassMethod RHTotal(Hours As %Float) As %Float [ SqlProc ]
> {
> I '$D(%RHTOTAL) S %RHTOTAL=0
> S %RHTOTAL=%RHTOTAL+Hours
> Q %RHTOTAL
> }
>
> ClassMethod RMTotal(Money As %Float) As %Float [ SqlProc ]
> {
> I '$D(%RMTOTAL) S %RMTOTAL=0
> S %RMTOTAL=%RMTOTAL+Money
> Q %RMTOTAL
> }
>
> Query qryContractorWorkHistory(Contractor As %String, Year As %Integer)
> As %SQLQuery(CONTAINID = 1)
> {
> SELECT Contractor->Name,{fn Floor(ContractorWorkMonth)}
> ContractorWorkMonth,Depositdate,Hours, SUM(Hours %FOREACH({fn
> Floor(ContractorWorkMonth)},DepositDate)) DDHours,
> NLS.CONTRACTORBATCH_RHTotal(Hours) RTHours,Money, SUM(Money
> %FOREACH(DepositDate)) DDMoney, NLS.CONTRACTORBATCH_RMTotal(Money)
> RTMoney, SUM(Hours) As TH, SUM(Money) As MONEY,AdjustmentType FROM
> NLS.CONTRACTORBATCH
> Where Contractor=:Contractor and ContractorWorkMonth %STARTSWITH :Year
> group by {fn
> Floor(ContractorWorkMonth)},DepositDate,AdjustmentType,Hours,Money
> order by {fn Floor(ContractorWorkMonth)},DepositDate,AdjustmentType
> ASC,Hours,Money
> }
>
> One problem with this approach is that I had to write a seperate sqlproc
> for each column I want to keep a running total on, and the other problem
> is that using the % variables, if I execute the query a second time from
> the same session, the old sum is still in the % variable, so how do I
> know I am executing another query so I can reset the variable to 0?
>
> Thanks for your help,
>
> Eric Gibson
