According to SMHI autumn is when the average temperature of the day is falling and is between 0 and 10 degrees. And spring is when it’s rising and between 0 and 10 degrees.
Reference: http://www.smhi.se/cmp/jsp/polopoly.jsp?d=5938&l=sv
This “little” script will give you a resulting table with average temperature on UtomhusNorr calculated with the last five days.
IF EXISTS(SELECT TABLE_NAME FROM TEMPDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#TemperatureAvg%')
DROP TABLE #TemperatureAvg
GO
CREATE TABLE #TemperatureAvg (
TimeStamp DATETIME NOT NULL,
Temperature DECIMAL(18, 3) NOT NULL,
)
DECLARE
@query VARCHAR(2000),
@currDate DATETIME
DECLARE dateCurr CURSOR
FOR
SELECT DISTINCT CAST(CONVERT(CHAR(10),TimeStamp,20) AS DATETIME) as TempDates
FROM UtomhusNorr
WHERE TimeStamp > DATEADD(month, -1, GETDATE())
ORDER BY TempDates DESC
FOR READ ONLY
OPEN dateCurr
FETCH NEXT FROM dateCurr INTO @currDate
WHILE (@@FETCH_STATUS <> -1) BEGIN
SET @Query = 'SELECT MAX(TimeStamp) AS TimeStamp, AVG(Temperature) as Temperature FROM UtomhusNorr WHERE TimeStamp > DATEADD(day, -5, ''' + CONVERT(NVARCHAR(32), @currDate) + ''') AND TimeStamp < ''' + CONVERT(NVARCHAR(32), @currDate) + ''''
INSERT #TemperatureAvg EXEC (@query)
FETCH NEXT FROM dateCurr INTO @currDate
END
CLOSE dateCurr
DEALLOCATE dateCurr
SELECT TimeStamp, Temperature AS AvgTemp5daysBack FROM #TemperatureAvg
DROP TABLE #TemperatureAvg
