Analytic functions (see Lead, Lag, etc) in the SQL Reference) are perfect
for thise type of thing:
SELECT
TIMESTAMP,
ASSET,
ASSET_STAT,
(LEAD(TIMESTAMP,1,SYSDATE) OVER (ORDER BY TIMESTAMP) - TIMESTAMP) * 1440
AS STATUS_DURATION_MIN
FROM T2
ORDER BY TIMESTAMP;
"Scotty" <scotty_at_imstressed.com> wrote in message
news:a274d110.0207290736.1adbc42e_at_posting.google.com...
> 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.
Received on Mon Jul 29 2002 - 17:38:23 CDT
