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]
Expected output:
SampleDate PeakWind AverageWind WindDirAtPeakWind
----------------------------------------------------------
15/01/01 3.9 1.18587301587302 78
15/01/02 4.6 1.60222531293463 85
15/01/03 6.6 1.86013888888888 26
I can't seem to find the proper JOIN or sub query to get the result.
Any ideas?
DDL
CREATE TABLE [dbo].[WeatherData](
[SampleDateTime] [datetime] NULL,
[StationID] [smallint] NULL,
[SampleDate] [nchar](10) NULL,
[SampleTime] [nchar](10) NULL,
[WindSpeed_AVG_2MIN] [float] NULL,
[WindGust_AVG_2MIN] [float] NULL,
[WindDir_AVG_2MIN] [float] NULL,
[WindSpeed_AVG_10MIN] [float] NULL,
[WindGust_AVG_10MIN] [float] NULL,
[WindDir_AVG_10MIN] [float] NULL,
[AirTemp] [float] NULL,
[RelHumidity] [float] NULL,
[DewPoint] [float] NULL,
[Pyranometer] [float] NULL,
[Quantum] [float] NULL,
[AirPressure] [float] NULL,
[SnowLevel] [float] NULL,
[MeltedPrecip] [float] NULL,
[PW_Current] [char](10) NULL,
[PW_15MIN] [char](10) NULL,
[PW_60MIN] [char](10) NULL,
[PW_Vis] [float] NULL,
[Visibility] [float] NULL,
[CloudBase_1] [float] NULL,
[CloudBase_2] [float] NULL,
[CloudBase_3] [float] NULL,
[VerticalVis] [float] NULL,
[Batt_VDC] [float] NULL,
[BIT] [char](10) NULL
)
Example Data
SampleDateTime StationID SampleDate SampleTime WindSpeed_AVG_2MIN WindGust_AVG_2MIN WindDir_AVG_2MIN WindSpeed_AVG_10MIN WindGust_AVG_10MIN WindDir_AVG_10MIN
2015-01-31 23:59:06.000 100 15/01/31 03:03:58 16.1 19.3 25 15.6 19.3 27
2015-01-31 23:57:06.000 100 15/01/31 03:01:58 15.8 19.3 28 15.5 19.3 27
2015-01-31 23:55:05.000 100 15/02/01 02:59:58 9.8 10.9 16 8.8 11.1 19
2015-01-31 23:53:05.000 100 15/02/01 02:57:58 9.7 10.9 16 8.5 11.1 20
Aucun commentaire:
Enregistrer un commentaire