Results 1 to 4 of 4

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

  1. #1
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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.

  4. #4
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Jeffrey, I'm thinking SQL Server here, but I would suspect Access will have similar behavior. In your IN clause, you already have...

    Code:
    AND m2.Date>=#3/1/2010# And m2.Date<#4/1/2010#
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •