Re: SQL puzzle

From: Marc Blum <marc_at_marcblum.de>
Date: Sat, 16 Feb 2002 20:54:04 GMT
Message-ID: <3c6ec682.43405563@news.online.de>

ok, this one will do it (8.1.6 and above):

SELECT * FROM (
SELECT device,

       nvl(begin_time,
       lag(begin_time) over (ORDER BY r)) AS begin_time,
       end_time,
       end_flag,
       status

FROM (
SELECT r,

       device,
       TIMESTAMP,
       status,
       voriger_status,
       naechster_status,
       naechster_timestamp,
       CASE
          WHEN status != nvl(voriger_status,'_') THEN TIMESTAMP
          ELSE null 
       END AS begin_time,
       CASE
          WHEN naechster_status IS NULL THEN null      
          WHEN status != naechster_status THEN naechster_timestamp

          ELSE null
       END AS end_time,
       CASE
          WHEN naechster_status IS NULL THEN 'y'      
          WHEN status != naechster_status THEN 'y'      
          ELSE null
       END AS end_flag

  FROM (
select device,

       TIMESTAMP,
       status,
       lag(status) over(PARTITION BY device ORDER BY timestamp)
voriger_status,
       lead(status) over(PARTITION BY device ORDER BY timestamp)
naechster_status,
       lead(timestamp) over(PARTITION BY device ORDER BY timestamp)
naechster_timestamp,
       rownum r      

  from device_log t
) v ) v

WHERE begin_time IS NOT NULL OR

      end_time IS NOT NULL
) v

WHERE end_flag = 'y'
/

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Feb 16 2002 - 14:54:04 CST