vendredi 31 juillet 2015

SQL Select (Value A at max value B) and other computed values (avg/min/max)

I have some weather data stored in a SQL Server database. The relevant columns are

[SampleDate], [SampleDateTime], [WindSpeed_Avg_2MIN], [WindDir_AVG_2MIN]

and store data at 2 minute intervals. The code gives me a summary by day for a month

SELECT 
    [SampleDate], 
    Max([WindSpeed_Avg_2MIN]) as PeakWind, 
    Avg([WindSpeed_Avg_2MIN]) as AverageWind
FROM
   (SELECT 
        [SampleDateTime], 
        [WindSpeed_Avg_2MIN],
        [WindDir_AVG_2MIN] 
    FROM 
        WeatherData
    WHERE 
        ((DATEPART(mm,SampleDateTime) = @Month) 
        AND (DATEPART(yyyy,SampleDateTime) = @Year))) as tblA
GROUP BY 
    [SampleDate]
ORDER BY 
    [SampleDate]

Which yields

SampleDate  PeakWind    AverageWind
----------------------------------------
15/01/01    3.9         1.18587301587302
15/01/02    4.6         1.60222531293463
15/01/03    6.6         1.86013888888888

What I want is to add a column that would show the Wind Direction [WindDir_AVG_2MIN] for the row that had the PeakWind. So for 720 rows in a day, I've got the AVG and MAX of [WindSpeed_Avg_2MIN], and I want to show the discrete value for [WindDir_AVG_2MIN] for the row that has the MAX of [WindSpeed_Avg_2MIN]

I can't seem to find the proper JOIN or sub query to get the result.

Any ideas?

Aucun commentaire:

Enregistrer un commentaire