Again: SQL puzzle

From: Marc Blum <marc_at_marcblum.de>
Date: Tue, 19 Feb 2002 19:24:22 GMT
Message-ID: <3c72a448.6981739@news.online.de>

Sorrily (fortunately?) I found two bugs in the SELECT-statement

here's the whole puzzle again:

DROP TABLE device_log;
CREATE TABLE device_log
(device VARCHAR2(200) NOT NULL,
 status VARCHAR2(5) NOT NULL,
 timestamp DATE NOT NULL);
--

INSERT INTO device_log
VALUES
('A','UP',TO_DATE('01.01.2000 00:15:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('A','UP',TO_DATE('01.01.2000 00:30:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('A','DOWN',TO_DATE('01.01.2000 00:45:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('A','UP',TO_DATE('01.01.2000 01:00:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('B','DOWN',TO_DATE('01.01.2000 00:15:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('B','DOWN',TO_DATE('01.01.2000 00:30:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('B','DOWN',TO_DATE('01.01.2000 00:45:00','dd.mm.yyyy hh24:mi:ss'));
--

INSERT INTO device_log
VALUES
('B','UP',TO_DATE('01.01.2000 01:00:00','dd.mm.yyyy hh24:mi:ss'));
--

COMMIT;
--

SELECT device,

       status,
       to_char(timestamp,'dd.mm.yyyy hh24:mi:ss')

  FROM device_log
 ORDER BY

       device,
       timestamp;

DEVICE  	STATUS 	TO_CHAR(TIMESTAMP,'DD.MM.YYYYH
A       		UP     		01.01.2000 00:15:00
A       		UP     		01.01.2000 00:30:00
A      		DOWN   	01.01.2000 00:45:00
A       		UP     		01.01.2000 01:00:00
B       		DOWN   	01.01.2000 00:15:00
B       		DOWN   	01.01.2000 00:30:00
B       		DOWN   	01.01.2000 00:45:00
B       		UP     		01.01.2000 01:00:00


SELECT device,
       TO_CHAR(begin_time,'dd.mm.yyyy hh24:mi:ss') as anfang,
       TO_CHAR(end_time,'dd.mm.yyyy hh24:mi:ss') as ende,
       status
  FROM (
         SELECT device,
                nvl(begin_time,lag(begin_time) over (PARTITION BY
device ORDER BY r)) AS begin_time,  -- => missing partition by device 
                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,
                                  rank() over (PARTITION BY device
ORDER BY TIMESTAMP) r -- => do not use ROWNUM
                             from device_log t
                          ) v
                 ) v
         WHERE begin_time IS NOT NULL OR
               end_time   IS NOT NULL
        ) v

 WHERE end_flag = 'y'
/

DEVICE	ANFANG				ENDE					STATUS
A			01.01.2000 00:15:00	01.01.2000 00:45:00	UP
A			01.01.2000 00:45:00	01.01.2000 01:00:00	DOWN
A			01.01.2000 01:00:00							UP
B			01.01.2000 00:15:00	01.01.2000 01:00:00	DOWN
B			01.01.2000 01:00:00							UP

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Tue Feb 19 2002 - 13:24:22 CST