OLAP question

From: <jo_holvoet_at_amis.com>
Date: Tue, 02 Mar 2004 17:52:19 +0100
Message-id: <OFAB613BA1.123752D4-ONC1256E4B.005AE838@eu.amis.com>

Hi all,

I have a dataset relating to equipment up/downtime. Example :

equipment date_time status delta_secs

...

A1              01-JAN-2004 00:00:00    down    15
A1              01-JAN-2004 00:00:15    down    20
A1              01-JAN-2004 00:00:35    up      600
A1              01-JAN-2004 00:10:35    down    25
A1              01-JAN-2004 00:11:00    up      1500

...

I would like to have an overview of downtimes like :

...

A1      01-JAN-2004 00:00:00            35 secs
A1      01-JAN-2004 00:10:35            25 secs

...

Can I do this with analytic functions ? When I start with something like :

select equipment,

        date_time,
        sum(delta_secs) over (partition by equipment, status order by

equipment, date_time)
from dataset

then of course I get something like :

A1      01-JAN-2004 00:00:00            35 secs
A1      01-JAN-2004 00:10:35            60 secs

(the second downtime is added to the first).

Am I missing something obvious ?

mvg/regards

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 10:49:35 CST