Calculate 24hour rolling average from intermittent hourly data

PHOTO EMBED

Thu Sep 14 2023 19:11:46 GMT+0000 (Coordinated Universal Time)

Saved by @dhfinch

SELECT WRec.StationName, WRec.DateTimeUTC
     , AVG(WRange.QAQC_Conc) AS R24Hr_Conc, COUNT(WRange.QAQC_Conc) AS R24Hr_Count
	 , Min(WRange.DateTimeUTC) AS MinUTC, Max(WRange.DateTimeUTC) AS MaxUTC
	 , Max(WRange.QAQC_Conc) AS MaxConc, Min(WRange.QAQC_Conc) AS MinConc
FROM QAQCData WRec
JOIN QAQCData WRange 
  ON WRec.StationName=WRange.StationName 
 AND  DATEDIFF(hour,WRange.DateTimeUTC, WRec.DateTimeUTC ) BETWEEN 0 AND 23
 AND WRange.IsValidData=1
WHERE LTRIM(RTRIM(WRec.StationName)) LIKE LTRIM(RTRIM(@StationName))
GROUP BY WRec.StationName, WRec.DateTimeUTC
ORDER BY WRec.DateTimeUTC DESC
content_copyCOPY