Ansi SQL for timestamp to intervals query

Question

Let's say I have a table which tells me when a certain thing was activated and deactivated:

CREATE TABLE [dbo].[active] 
( 
    [timestamp] [DATETIME] NOT NULL, 
    [activated] [BIT] NOT NULL 
) 

And my best attempt at making a query against this to retrieve the intervals for which the thing was active:

SELECT a.timestamp AS 'ActivatedDate', 
       Deactivated.timestamp 
FROM   active a 
       OUTER apply (SELECT Min(d.timestamp) AS TimeStamp 
                    FROM   active d 
                    WHERE  activated = 0 
                           AND d.timestamp > a.timestamp) AS Deactivated 
WHERE  a.activated = 1 

I need to rewrite this so that it will work with ANSI SQL, or at least both T-SQL and Oracle 9. It is my understanding that OUTER apply wont work for Oracle 9.

If there is a much better way to do this sort of thing, please share! :)

I need this to check if certain events were happening while this thing was enabled/disabled. Thanks!

Answer 1

Use a correlated subquery:

select a.timestamp as ActivatedDate,
       (select min(d.timestamp)
        from active d
        where d.activated = 0 and
              d.timestamp > a.timestamp
       ) as DeactivedDate
from active a
where a.activated = 1;

This is ANSI SQL and it should work in SQL Server and Oracle (and almost all other databases).

Do note: there may be more efficient ways to do what you want in any given database.