5 mins report

Question

Post reply

5 mins report

sahana

SSCrazy

Points: 2530
More actions
June 6, 2005 at 6:18 pm

#76330

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Brian Selzer SSC-Addicted Points: 402 More actions · Answer 1

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

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 2

Michael Du Bois

SSCertifiable

Points: 5143

June 7, 2005 at 4:49 am

#563439

Bselzer that is very very cool.

Mike

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

...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]

Brian Selzer SSC-Addicted Points: 402 More actions · Answer 4

Brian Selzer

SSC-Addicted

Points: 402

June 7, 2005 at 6:34 am

#563466

OOPS! You're right, it should be / 5) * 5

Brian
MCDBA, MCSE+I, Master CNE

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

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

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 6

Michael Du Bois

SSCertifiable

Points: 5143

June 7, 2005 at 1:30 pm

#563648

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