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