Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL puzzle

Re: SQL puzzle

From: Karsten Schmidt <groups_at_karsten-schmidt.com>
Date: 18 Feb 2002 00:00:50 -0800
Message-ID: <c6711ac4.0202180000.5de309fa@posting.google.com>


Thanks a million to all of you.

When i get some time, i will benchmark the three suggested solutions and post results here.

Karsten

marc_at_marcblum.de (Marc Blum) wrote in message news:<3c6ec782.43661702_at_news.online.de>...
> 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 Mon Feb 18 2002 - 02:00:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US