Re: SQL puzzle

From: Lothar Armbruester <lothar.armbruester_at_t-online.de>
Date: Fri, 15 Feb 2002 21:40:12 +0100
Message-ID: <PM00039A0D036DDFF6@hades.none.local>

Karsten Schmidt wrote:
>
> I would like to have a result like
> Device status from to
> A up 10:00 10:45
> A down 10:45 10:48
> A up 10:48 (null)
> B up 9:00 (null)
>

Hello Karsten,
after some puzzling I came upon the following solution. It's not a performance wonder but it seems to work. Since the SQL*Plus Worksheet of Oracle9i highlights 'time' as a reserved word, I named the column in my test table 'tim'.

select

   device,
   status,
   min(tim_from) tim_from,
   tim_to
from

   (select

       a.device,
       a.tim tim_from,
       a.status,
       (select min(b.tim)
        from
           device_test b
        where
           a.device=b.device and
           a.tim<b.tim and
           a.status<>b.status) tim_to
    from
       device_test a)

group by

   device,
   status,
   tim_to
order by

   device,tim_from;

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de

Received on Fri Feb 15 2002 - 14:40:12 CST