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]

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