sorry, I renamed the table and the columns, here they are:
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;
--
On Sat, 16 Feb 2002 20:54:04 GMT, marc_at_marcblum.de (Marc Blum) wrote:
>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
regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de
Received on Sat Feb 16 2002 - 15:00:00 CST
