5 mins report
-
I am collecting data for one full day.That means I am getting 1440 records per one day. ANdon every minute I am collecting temperature of the enironment. But I wanted to generate a report by taking average of 5 consecutive miniutes.
Hour -- interval -- Avg.temp
0 -------0-4 -------23
0 -------5-9 ------- 24
0 -------10-14 -------23.4
......
.....
......
23 -------55-59 ------- 21.6
How can I do this report.
Thanks.
-
Assuming that your table is like:
CREATE TABLE temperatures
(
TempTime DATETIME NOT NULL,
Tempurature FLOAT NOT NULL
)
Here's your query:
SELECT [Hour], CAST(StartingInterval AS CHAR(2)) + '-' + CAST(StartingInterval + 4 AS CHAR(2)) AS interval, AverageTemperature as [Ave.temp]
FROM
(SELECT DATEPART(hh, TempTime) AS [Hour],
(DATEPART(mi, TempTime) / 12) * 12 AS StartingInterval,
AVG(Temperature) AS AverageTempurature
FROM temperatures
GROUP BY DATEPART(hh, TempTime), (DATEPART(mi, TempTime) / 12) * 12) a
Brian
MCDBA, MCSE+I, Master CNE -
Bselzer that is very very cool.
Mike
-
...and incorrect
Since we want 5 minutes buckets, it should read something like this:
SELECT [Hour],
REPLACE(STR(StartingInterval,2),' ','0') + '-' + REPLACE(STR(StartingInterval+4,2),' ','0') AS interval,
AverageTemperature as [Ave.temp]
FROM
(SELECT DATEPART(hh, TempTime) AS [Hour],
(DATEPART(mi, TempTime) / 5) * 5 AS StartingInterval,
AVG(Temperature) AS AverageTemperature
FROM temperatures
GROUP BY DATEPART(hh, TempTime), (DATEPART(mi, TempTime) / 5) * 5) a
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
OOPS! You're right, it should be / 5) * 5
Brian
MCDBA, MCSE+I, Master CNE -
I had to go another level to get the results.
SET NOCOUNT ON
DECLARE @temperatures TABLE
(
TempTime DATETIME,
Temperature NUMERIC(5,2)
)
DECLARE @CTR INT
DECLARE @Temp NUMERIC(5,2)
DECLARE @myDate DATETIME SET @myDate = '06/07/2005'
SET @CTR = 0
WHILE @CTR <= (24 * 60) -2
BEGIN
SET @myDate = DATEADD(MINUTE, 1, @myDate)
SELECT @Temp = CONVERT(NUMERIC(5,2), DATEPART(MINUTE, @myDate))
INSERT @temperatures VALUES (@myDate, @Temp)
SELECT @CTR = @CTR + 1
END
SELECT [Hour],
CONVERT(VARCHAR,StartingInterval) + ' - ' + CONVERT(VARCHAR,StartingInterval + 4) MinuteRange,
AverageTemperature
FROM
(
SELECT [Hour], StartingInterval, AVG(Temperature) AverageTemperature
FROM
(
SELECT DATEPART(hh, TempTime) AS [Hour],
(DATEPART(mi, TempTime) / 5) * 5 AS StartingInterval,
MIN(Temperature) AS Temperature
FROM @temperatures
GROUP BY TempTime, DATEPART(hh, TempTime), (DATEPART(mi, TempTime) / 5) * 5) A
GROUP BY [Hour], StartingInterval) B
ORDER BY [Hour], StartingInterval
Regards,
gova -
True Frank and I adjusted the code (including correcting the spelling errors) to yeild the results for the 5 min intervals and had intended to post both the corrected code and the data set I used to test it as I was a little rushed this morning I decided to just give a vote of thanks for nice logic.
Mike
-
Personally I would rather prefer to do
SELECT DATEADD(minute, -DATEPART(minute, TempTime) % 5, TempTime) AS StartingInterval,
AVG(Temperature) AS AverageTemperature
FROM temperatures
GROUP BY DATEADD(minute, -DATEPART(minute, TempTime) % 5, TempTime)
on the server and the rest, which is the mere presentation to me, at the client.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Totally agree with that Frank. Keep the server serving and the application applicating the presentation
. -
Nice to know that you agree with me.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It's rare that I don't... but that happens too
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply