Query: Average interval - 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

General SQL Server Forums

New to SQL Server Programming

Query: Average interval

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-09-22 : 02:24:47

Hi Guys.

I have a strange one here so please bare with me.

We have a ton of mobile units in the field.

They connect to our server at a specific interval.

I'm trying to figure out what that interval is.

The data looks like this.

mUnit Received Date
123456 2009-09-22 07:54:15.973
123111 2009-09-22 07:54:12.567
111443 2009-09-22 07:54:11.960
998776 2009-09-22 07:54:11.927
665443 2009-09-22 07:54:10.990
123456 2009-09-22 07:54:10.210
123111 2009-09-22 07:54:09.270
111443 2009-09-22 07:54:08.397
998776 2009-09-22 07:54:07.583
665443 2009-09-22 07:54:07.460
123456 2009-09-22 07:54:07.300
123111 2009-09-22 07:54:05.113
111443 2009-09-22 07:54:04.880
998776 2009-09-22 07:54:04.287
665443 2009-09-22 07:54:02.613
123456 2009-09-22 07:54:00.427
123111 2009-09-22 07:53:59.567
111443 2009-09-22 07:53:58.693

You will notice from te datset above mUnit 123456 averages a new message every 5 sec.

What query do i need to get that average interval for all mUnits.

Hope this helps.

Cheers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-22 : 03:31:05

[code]DECLARE @Sample TABLE
(
Unit INT,
Received DATETIME
)

INSERT @Sample
SELECT 123456, '20090922 07:54:15.973' UNION ALL
SELECT 123111, '20090922 07:54:12.567' UNION ALL
SELECT 111443, '20090922 07:54:11.960' UNION ALL
SELECT 998776, '20090922 07:54:11.927' UNION ALL
SELECT 665443, '20090922 07:54:10.990' UNION ALL
SELECT 123456, '20090922 07:54:10.210' UNION ALL
SELECT 123111, '20090922 07:54:09.270' UNION ALL
SELECT 111443, '20090922 07:54:08.397' UNION ALL
SELECT 998776, '20090922 07:54:07.583' UNION ALL
SELECT 665443, '20090922 07:54:07.460' UNION ALL
SELECT 123456, '20090922 07:54:07.300' UNION ALL
SELECT 123111, '20090922 07:54:05.113' UNION ALL
SELECT 111443, '20090922 07:54:04.880' UNION ALL
SELECT 998776, '20090922 07:54:04.287' UNION ALL
SELECT 665443, '20090922 07:54:02.613' UNION ALL
SELECT 123456, '20090922 07:54:00.427' UNION ALL
SELECT 123111, '20090922 07:53:59.567' UNION ALL
SELECT 111443, '20090922 07:53:58.693'

SELECT Unit,
DATEDIFF(SECOND, MIN(Received), MAX(Received)) / (COUNT(*) - 1) AS avgInterval
FROM @Sample
GROUP BY Unit
ORDER BY Unit[/code]

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-22 : 03:33:59

When I read the question earlier my first thought was: that's for Peso

No, you're never too old to Yak'n'Roll if you're too young to die.

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-09-22 : 06:10:22

Hi Peso...

I changed it up a bit to use the correct fields...

DECLARE @Sample TABLE
(
UnitNumber varchar(50),
createdate DATETIME
)
INSERT @Sample
SELECT UnitNumber,createdate from [Message]
SELECT UnitNumber,
DATEDIFF(SECOND, MIN(createdate), MAX(createdate)) / (COUNT(*) - 1) AS avgInterval
FROM @Sample
GROUP BY UnitNumber
ORDER BY UnitNumber

But get this error back...

(733594 row(s) affected)
Msg 8134, Level 16, State 1, Line 11
Divide by zero error encountered.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-22 : 07:25:56

[code]SELECT UnitNumber,
DATEDIFF(SECOND, MIN(CreateDate), MAX(CreateDate)) / (COALESCE(NULLIF(COUNT(*), 1), 2) - 1) AS avgInterval
FROM [Message]
GROUP BY UnitNumber
ORDER BY UnitNumber[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources