finding average earning over period of employment

It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

find the average monthly earning while you have been an affiliate

affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

spavgmonthlyearning affiliate_id

any help would be greatly appreciate even a point in the right direction would be fantastic.so this will do it.
is this pure sql or will it only run on sqlserver
thank you for your help

DECLARE @.maxdate as datetime
DECLARE @.mindate as datetime
set @.maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
set @.mindate = (select min(trans_dt) from dbo.affiliate_log)
select sum(earning)/datediff(M,@.mindate,@.maxdate)
from dbo.affiliate_log where affiliate_id = 1|||select DATEPART(m,date_field), avg(sales)
from table
group by DATEPART(m, date_field)|||It depends on what you mean by "average".

The simple average monthly earning would just be the total earnings divided by the total months.

select affiliate_id,
sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
from yourtable
group by affiliate_id

Or if they skip some months:

select affiliate_id,
sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
from yourtable
group by affiliate_id

Find out if a row is subtotal or not?

How do I check if a row is a subtotal or not? I have a report where one
column shows Average hour rate. Right now, the subtotal line shows the
Average column as a subtotal of all the other averages... So I'd like to
have a statement that does something like
if row is a subtitle row then
show nothing (or something else)
else
show the Average value
end if
I bet it's trivial, but I can't figure it out. Please help. :)
Kaisa M. LindahlDid you look at the InScope function? It will allow you to distinguish
between cells in subtotals and cells in the groupings. More information on
InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
E.g. =iif(InScope("ColumnGroup"), Avg(Fields!F1.Value), Nothing)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:%23glpsPj1EHA.3468@.TK2MSFTNGP14.phx.gbl...
> How do I check if a row is a subtotal or not? I have a report where one
> column shows Average hour rate. Right now, the subtotal line shows the
> Average column as a subtotal of all the other averages... So I'd like to
> have a statement that does something like
> if row is a subtitle row then
> show nothing (or something else)
> else
> show the Average value
> end if
> I bet it's trivial, but I can't figure it out. Please help. :)
> Kaisa M. Lindahl
>