Query to identify gaps in time data

I'm pretty new to database programming, and I'm in need some help writing what seems to be a complicated t-sql query.

Our database stores time data along with various levels recorded at that time. A typical measurement will last for weeks or months. I need to write a query in开发者_如何学Python SqlServer2005 to identify gaps in the time data to know when the instrument wasn't communicating. Our sample interval can be either 1 sec or .5 sec, but it will always be the same in a given measurement (ie: all samples in a measurement will be either 1 or .5 samples/sec).

Ideally I would like to get a list of [block1-start block1-end] [block2-start block2-end] etc

where each block is a unit of consecutive times within the overall measurement.

Are there any commands in TSQL that would make this query easier?

Check this out. Since you don't have overlapping intervals you can write a simple SQL query to return these results. The SQL below creates a dummy table variable called @Events to simulate your measurements table. The final query outputs the gaps greater than 1 second (configurable via variable @MaxIntervalAllowedBetweenEvents).

-- table with dummy data
declare @Events table (
    ID          int IDENTITY NOT NULL,
    StartDate   datetime NOT NULL,
    EndDate     datetime NOT NULL
)
INSERT @Events VALUES ('1/1/2011 1:00am', '1/1/2011 2:00am')
INSERT @Events VALUES ('1/1/2011 2:00am', '1/1/2011 3:00am')  -- no gap after previous event
INSERT @Events VALUES ('1/1/2011 3:01am', '1/1/2011 4:00am')  -- 1 minute gap
INSERT @Events VALUES ('1/1/2011 4:30am', '1/1/2011 5:00am')  -- 30 minute gap


-- this variable defines the maximum interval allowed between events
declare @MaxIntervalAllowedBetweenEvents int
set @MaxIntervalAllowedBetweenEvents = 1    -- # seconds


-- select the gaps between events
SELECT
    e1.EndDate,
    Min(e2.StartDate) as NextEventStartDate,
    DateDiff(s, e1.EndDate, Min(e2.StartDate)) as SecondsBetweenEvents
FROM
    @Events as e1
join
    -- for each event in e1, get the event that immediately follows it
    @Events as e2
        on  (e1.EndDate <= e2.StartDate)
GROUP BY
    e1.EndDate
HAVING
    -- filter out events that are too close to each other
    (DateDiff(s, e1.EndDate, Min(e2.StartDate)) > @MaxIntervalAllowedBetweenEvents)
ORDER BY
    e1.EndDate

Oracle has a lot of great analytical functions for this.

I think would you need to do is create a cursor from a sql statement that will order you results by Date, StartTime,EndTime.

A table that takes a Date input can be used like this to get the time between logins...

Omit the ROW_NUMBER bit if your table already has an Identity on it.

{select 
    Start.ID
    ,Done.ID
    ,Start.<Date_Column>
    ,Done.<Date_Column>
    ,DATEDIFF(s,Start.<Date_Column>,Done.<Date_Column>) AS InBetween
from 
    (SELECT
        ROW_NUMBER() OVER (ORDER BY <Date_Column>) AS ID
        ,<Date_Column>
    FROM
        <Tablename> (NOLOCK)
    ) START
    INNER JOIN (
        SELECT
            ROW_NUMBER() OVER (ORDER BY <Date_Column>) AS ID
            ,<Date_Column>
        FROM
            <Tablename> (NOLOCK)
        ) DONE  ON START.ID + 1 = DONE.ID
ORDER BY
    DATEDIFF(s,Start.<Date_Column>,Done.<Date_Column>)DESC}