Correlated subquery that returns avg of top 10 readings by month.

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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:

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
 
Oops, that 2nd code segment was supposed to be:
Code:
SELECT DAMD, DATESERIAL(YEAR(m.Date), MONTH(m.Date),1)
 FROM (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) ), Meter m
GROUP BY DATESERIAL(YEAR(m.Date), MONTH(m.Date),1), DAMD
 
I think a correlated subquery will be too slow, given I'm dealing with half hourly records.
This works:
Code:
SELECT m.kVA,  DATESERIAL(YEAR(m.Date), MONTH(m.Date),1) as MONTH FROM Meter2 m
WHERE m.kVA IN
(SELECT TOP 10 m2.kVA  
FROM Meter2 m2
WHERE  DATESERIAL(YEAR(m2.Date), MONTH(m2.Date),1) =  DATESERIAL(YEAR(m.Date), MONTH(m.Date),1)
AND m2.Date>=#3/1/2010# And m2.Date<#4/1/2010#
ORDER BY m2.kVA DESC)
AND m.Date>=#3/1/2010# And m.Date<#4/1/2010#
...but it takes for ever, and this is hard coded for just 2 months. If I took the hard coding out, Access crashes.


So I guess I'll have to work out how to write an Access macro that loops though my original query and increases the m.Date period by one on each subsequent pass.
 
Jeffrey, I'm thinking SQL Server here, but I would suspect Access will have similar behavior. In your IN clause, you already have...

Code:
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] m2[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]#3[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]1[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]2010# [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]And[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] m2[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]<[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]#4[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]1[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]/[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]2010#[/COLOR][/SIZE]

You also have it in your outer where clause, which is causing an un-needed evaluation since you have already restricted your records once. Take out the outer one, and see if that helps any. Also, any way you could post some table structure and sample data?

Greg
 
Back
Top