Grouping every 10 seconds - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping every 10 seconds

Author

Topic

fullmonte
Starting Member

4 Posts

Posted - 2009-07-09 : 12:11:25

Hi,

I have the following data

Occured On | Value Average | Calculations | Accumulated
------------------- ---------------- ------------- -----------
2009-07-09 11:03:44 11.0131893 32 32
2009-07-09 11:03:55 8.02417182 32 64
2009-07-09 11:03:58 10.8869667 32 96
2009-07-09 11:04:01 9.75629329 32 128

Which is generated with the following query

SELECT
sm.occuredOn as 'Occured On',
sm.valueAverage as 'Value Average',
sm.calculationsCount as 'Calculations Count',
(SELECT SUM(sm2.calculationsCount) FROM SummaryMessage sm2 WHERE sm2.occuredOn <= sm.occuredOn) AS Accumulated
FROM
SummaryMessage sm
ORDER BY
1

I would like to group value average and accumulated every 10 seconds.

This is exceeding my T-SQL expertise so any help will be much appreciated.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-09 : 13:41:34

Do you mean like 11:03:00,11:03:10,11:03:20, etc. for ValueAverage?

Jim

fullmonte
Starting Member

4 Posts

Posted - 2009-07-09 : 14:03:22

Right given this data

Occured On | Value Average | Calculations | Accumulated
------------------- ---------------- ------------- -----------
2009-07-09 11:03:44 11.0131893 32 32
2009-07-09 11:03:55 8.02417182 32 64
2009-07-09 11:03:58 10.8869667 32 96
2009-07-09 11:04:01 9.75629329 32 128

I would like to get

11:03:44 AVG(11.01) SUM(32)
11:03:54 AVG(11.01) SUM(32)
11:04:04 AVG(8.02;10.88;9.75) SUM(32;32;32)

Make sense?

Thanks for your help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-09 : 14:55:14

The problem just got too big for my brain. The following rounds things in to 10 second intervals and may give you a starting point.

DECLARE @Table TABLE (OccurredOn Datetime,strTime char(8),Average float,Calculations int,Accumulated int)

INSERT INTO @Table

SELECT '2009-07-09','11:03:44', 11.0131893, 32, 32 UNION
SELECT '2009-07-09','11:03:55', 8.02417182, 32 ,64 UNION
SELECT '2009-07-09','11:03:58', 10.8869667, 32 ,96 UNION
SELECT '2009-07-09','11:04:01', 9.75629329,32 ,128

select convert(datetime,OccurredOn+strTime)
, DATEADD(s,((datediff(s,convert(datetime,OccurredOn),convert(datetime,OccurredOn+strTime))+10) /10 )*10 ,convert(datetime,OccurredOn))
from
@table

Jim

fullmonte
Starting Member

4 Posts

Posted - 2009-07-09 : 15:40:03

Thanks Jim, I will give it a try.

Matias

fullmonte
Starting Member

4 Posts

Posted - 2009-07-09 : 19:54:40

This is what I eneded up doing.

DECLARE @firstMessage AS datetime
DECLARE @intervalInSeconds AS int
SET @intervalInSeconds = 30

SELECT TOP 1
@firstMessage = occuredOn FROM SummaryMessage
ORDER BY occuredOn ASC

SELECT
AVG(sm.valueAverage) as 'Value Average',
SUM(sm.calculationsCount) as 'Messages Processed',
DATEDIFF(second, @firstMessage, sm.occuredOn) / @intervalInSeconds * @intervalInSeconds as 'Timeline'
FROM
SummaryMessage sm
GROUP BY
DATEDIFF(second, @firstMessage, sm.occuredOn) / @intervalInSeconds * @intervalInSeconds
ORDER BY
Timeline

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources