Get a set of date/time ranges based on DB data

Question

I've got a SQL 2005 DB full of activity records that include a DateTime column, a DeviceID, and various bits of data. I need a query that will give me a list of date/time ranges based on DeviceID (possibly limited by a start/end time). Also, I would consider a new range as starting at least 60min after the old one ended (if it's less than 60min, then it's part of the old range).

Example data:

DateTime                 DeviceID
2009-10-11 12:01:30      Dev1
2009-10-11 12:02:30      Dev1
2009-10-11 12:03:21      Dev1
2009-10-11 12:04:30      Dev1
2009-10-11 12:05:45      Dev1
2009-10-11 12:06:10      Dev1
2009-10-11 12:07:40      Dev1
2009-10-11 12:08:20      Dev1
2009-10-11 13:30:11      Dev1
2009-10-11 13:32:21      Dev1
2009-10-11 13:36:45      Dev1
2009-10-11 13:39:16      Dev1
2009-10-11 13:42:18      Dev1
2009-10-11 13:46:22      Dev1
2009-10-11 13:48:35      Dev1

Expected Results:

StartDate                EndDate
2009-10-11 12:01:30      2009-10-11 12:08:20
2009-10-11 13:30:11      2009-10-11 13:48:35

Answer 1

Read something about ROW_NUMBER():

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Then look at this query:

SELECT
   DeviceID,
   "DateTime",
   ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY "DateTime") OrdinalNumber
FROM TableName

Then look at this query:

WITH DateTable
AS
(SELECT
   DeviceID,
   "DateTime",
   ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY "DateTime") OrdinalNumber
FROM TableName)
SELECT
   D1.DeviceID,
   D1."DateTime" EndDate,
   D2."DateTime" StartDate
FROM DateTable D1
JOIN DateTable D2
ON (D1.DeviceID = D2.DeviceID)
   AND (D1.OrdinalNumber = D2.OrdinalNumber - 1)
   AND (DATEDIFF(MINUTE,D1."DateTime",D2."DateTime") >= 60)

We are looking for gaps with more than 60 minute difference.

Now You have to take StartDate from every row and match it with EndDate from next. Since it is 4AM, I'll leave it to You:)