Re: Calculating Total Time

> Hello all....
>
> I need a little PL/SQL help.
>
> I have at simple table
>
> Timestamp Asset Status
> 2002-07-01 12:00 Pump 1 OPEN
> 2002-07-01 2:00 Pump 1 CLOSED
> 2002-07-01 3:30 Pump 1 OPEN
> 2002-07-01 3:45 Pump 1 CLOSED
> 2002-07-02 15:45 Pump 1 OPEN
> 2002-07-02 18:15 Pump 1 CLOSED
> .
> .
> .
> You get the idea.
>
> I would like to calculate the total time Pump 1 was OPEN and total
> time Pump 1 was closed for a given starting time period and ending
> time period.
>
> Is this what Time Series is good for? Or is there an straight PL/SQL
> algorithm I can use?
>
> Thanks.

Try this:

drop table t2;
create table t2 (
  timestamp date,
  asset varchar2(10),
  status varchar2( 6)
);

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

insert into t2 values ('01.07.2002 12:00:00','Pump 1','OPEN'  );
insert into t2 values ('01.07.2002 14:00:00','Pump 1','CLOSED'); 
insert into t2 values ('01.07.2002 15:30:00','Pump 1','OPEN'  );
insert into t2 values ('01.07.2002 15:45:00','Pump 1','CLOSED'); 
insert into t2 values ('02.07.2002 15:45:00','Pump 1','OPEN'  );
insert into t2 values ('02.07.2002 18:45:00','Pump 1','CLOSED'); 

select sum(
  case
    when status = 'OPEN'
    then to_number( sysdate-timestamp)
    else to_number((sysdate-timestamp)*-1)   end) * 1440 "Time Open"
from t2;

Hth

Rene

-- 
Latest article on my HP (as of 14.07.2002):
  Transforming each character in a string with perl
  http://www.adp-gmbh.ch/perl/each_char.html