Hi all. I've got some info from an electricty meter, and I've managed to get it to return the Average of the Top 10 readings for the month of May 2009, as per below:
But I want it to return the Average of the Top 10 readings for EVERY month in the m.Date field...not just the hard-coded month. I'm guessing this needs a correlated subquery, or something else trick, but I can't seem to work it out. Have tried so many things that it's not worth listing them all here.
THis query appends my figure for May onto a month by month list of dates:
...which is the general form I want to take, but just not correlated. i.e. every month has May 2009's figure against it.
Anyone know how I can achieve this?
Cheers
Jeff
Code:
SELECT AVG(kVA) AS DAMD
FROM (Select TOP 10 m.kVA from meter m
WHERE m.Date>=#5/1/2009# And m.Date<#6/1/2009#
AND HOUR(m.StartTime) >= 8 AND HOUR(m.StartTime) <20
AND m.DayType = 'BD'
ORDER BY m.kVA DESC)
But I want it to return the Average of the Top 10 readings for EVERY month in the m.Date field...not just the hard-coded month. I'm guessing this needs a correlated subquery, or something else trick, but I can't seem to work it out. Have tried so many things that it's not worth listing them all here.
THis query appends my figure for May onto a month by month list of dates:
Code:
SELECT DAMD, DATESERIAL(YEAR(m.Date), MONTH(m.Date),1)
FROM (SELECT AVG(kVA) AS DAMD
FROM (Select TOP 10 (((2*m.kWh)^2 + (2*m.kVarh)^2)^0.5) as kVA from meter m
WHERE m.Date>=#5/1/2009# And m.Date<#6/1/2009#
AND HOUR(m.StartTime) >= 8 AND HOUR(m.StartTime) <20
AND m.DayType = 'BD'
ORDER BY (((2*m.kWh)^2 + (2*m.kVarh)^2)^0.5) DESC) ), Meter m
GROUP BY DATESERIAL(YEAR(m.Date), MONTH(m.Date),1), DAMD
...which is the general form I want to take, but just not correlated. i.e. every month has May 2009's figure against it.
Anyone know how I can achieve this?
Cheers
Jeff