How to determine when a time stamp does not exist in a table

Question

I have a table that receives data on an hourly basis. Part of this import process writes the timestamp of the import to the table. My question is, how can I build a query to produce a result set of the periods of time when the import did not write to the table?

My first thought is to have a table of static int and just do an outer join and look for nulls on the right side, but this seems kind of sloppy. Is there a more dynamic way to produce a result set for the times the import failed based on the timestamp?

This is a MS SQL 2000 box.

Update: I think I've got it. The two answers already provided are great, but instead what I'm working on is a function that returns a table of the values I am looking for for a given time frame. Once I get it finished I'll post the solution here.

Answer 1

Here's a slightly modified solution from this article in my blog:

• Flattening timespans: SQL Server

 

DECLARE @t TABLE
        (
        q_start DATETIME NOT NULL,
        q_end DATETIME NOT NULL
        )
DECLARE @qs DATETIME
DECLARE @qe DATETIME
DECLARE @ms DATETIME
DECLARE @me DATETIME
DECLARE cr_span CURSOR FAST_FORWARD
FOR
SELECT  s_timestamp AS q_start,
        DATEADD(minute, 1, s_timestamp) AS q_end
FROM    [20090611_timespans].t_span
ORDER BY
        q_start
OPEN    cr_span
FETCH   NEXT
FROM    cr_span
INTO    @qs, @qe
SET @ms = @qs
SET @me = @qe
WHILE @@FETCH_STATUS = 0
BEGIN
        FETCH   NEXT
        FROM    cr_span
        INTO    @qs, @qe
        IF @qs > @me
        BEGIN
                INSERT
                INTO    @t
                VALUES (@ms, @me)
                SET @ms = @qs
        END
        SET @me = CASE WHEN @qe > @me THEN @qe ELSE @me END
END
IF @ms IS NOT NULL
BEGIN
        INSERT
        INTO    @t
        VALUES  (@ms, @me)
END
CLOSE   cr_span

This will return you the consecutive ranges when updates did happen (with a minute resolution).

If you have an index on your timestamp field, you may issue the following query:

SELECT  *
FROM    records ro
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    records ri
        WHERE   ri.timestamp >= DATEADD(minute, -1, ro.timestamp)
                AND ri.timestamp < ro.timestamp
        )

Answer 2

I was thinking something like this:

select 'Start' MissingStatus, o1.LastUpdate MissingStart
from Orders o1
left join Orders o2
on o1.LastUpdate between 
  dateadd(ss,1,o2.LastUpdate) and dateadd(hh,1,o2.LastUpdate)
where o2.LastUpdate is null
union all
select 'End', o1.LastUpdate MissingEnd
from Orders o1
left join Orders o2
on o1.LastUpdate between 
 dateadd(hh,-1,o2.LastUpdate) and dateadd(ss,-1,o2.LastUpdate)
where o2.LastUpdate is null
order by 2