Re: SQL puzzle

From: DStevens <dstevens_at_navidec.com>
Date: Fri, 15 Feb 2002 09:45:17 -0700
Message-ID: <a4jduu$dln$1@newsreader.mailgate.org>

Will be slow ...

Create table bogus (obj char(1), status varchar2(3), thedate date);

insert into bogus values('A','ON', to_date('01/01/02 8:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('B','ON', to_date('01/01/02 8:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('C','ON', to_date('01/01/02 10:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('A','OFF', to_date('01/01/02 11:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('A','ON', to_date('01/01/02 12:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('B','OFF', to_date('01/01/02 12:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('D','ON', to_date('01/01/02 13:00:00','mm/dd/yy hh24:mi:ss'));
insert into bogus values('A','OFF', to_date('02/01/02 8:00:00','mm/dd/yy hh24:mi:ss'));

Select a.obj, a.status, 'Start Date ', a.thedate, 'Stop Date ', to_char(b.thedate), 'Span (in days)', to_char(b.thedate - a.thedate) from bogus a, bogus b
where a.obj = b.obj

and a.status != b.status
and b.thedate = (select min(thedate) from bogus c where c.thedate >= a.thedate
and c.status != a.status and c.obj = a.obj)

UNION ALL
Select a.obj, a.status, 'Start Date ', a.thedate, 'Stop Date ', 'NULL', 'Span (in days)', 'NULL'
from bogus a
where NOT EXISTS (select 'x' from bogus b where a.obj = b.obj and b.theDAte > a.theDate);

O STA 'STARTDATE' THEDATE 'STOPDATE' TO_CHAR(B.THEDATE)

'SPAN(INDAYS)' TO_CHAR(B.THEDATE-A.THEDATE)
- --- ----------- ------------------- ---------- -------------------
-------------- ----------------------------------------

A ON Start Date 01/01/2002 08:00:00 Stop Date 01/01/2002 11:00:00 Span (in days) .125
A ON Start Date 01/01/2002 12:00:00 Stop Date 02/01/2002 08:00:00 Span (in

days) 30.8333333333333333333333333333333333333

A OFF Start Date 01/01/2002 11:00:00 Stop Date 01/01/2002 12:00:00 Span (in

days) .041666666666666666666666666666666666667

B ON Start Date 01/01/2002 08:00:00 Stop Date 01/01/2002 12:00:00 Span (in

days) .166666666666666666666666666666666666667
C ON  Start Date  01/01/2002 10:00:00 Stop Date  NULL                Span (in
days) NULL
B OFF Start Date  01/01/2002 12:00:00 Stop Date  NULL                Span (in
days) NULL
D ON  Start Date  01/01/2002 13:00:00 Stop Date  NULL                Span (in
days) NULL
A OFF Start Date  02/01/2002 08:00:00 Stop Date  NULL                Span (in

days) NULL

8 rows selected.

Karsten Schmidt wrote:

> Hi all,
> I am trying to come up with a sql query that solves the following question:
>
> I have a table like
>
> DEVICE time status
> A 10:00 up
> A 10:30 up
> A 10:45 down
> A 10:48 up
> B 9:00 up
> B 10:00 up
> ... etc, i guess you get the idea.
> the rows represent samples of device availability.
>
> 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)
>
> I have been trying to figure this out for quite a while, all i could
> come up with was a stored procedure that populates a table.
>
> I would really appreciate some help on this. maintaining that separate table
> is both slow and hard to keep in sync.
>
> This is for a datawarehousing application on Oracle 8.1.7.2 EE / HP 64 Bit
>
> Karsten
Received on Fri Feb 15 2002 - 10:45:17 CST