Re: difference between 2 dates ?

From: DStevens <dstevens_at_navidec.com>
Date: Wed, 13 Feb 2002 10:11:32 -0700
Message-ID: <a4e6o7$5jf$1@newsreader.mailgate.org>

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, 'On Date ', a.thedate, 'Off Date ', b.thedate, 'Span (in days)', b.thedate - a.thedate
from bogus a, bogus b
where a.obj = b.obj

and a.status = 'ON'
and b.status = 'OFF'
and b.thedate = (select min(thedate) from bogus c where c.thedate >= a.thedate
and c.status = 'OFF' and c.obj = a.obj);

O 'ONDATE' THEDATE             'OFFDATE' THEDATE             'SPAN(INDAYS)'
B.THEDATE-A.THEDATE
- -------- ------------------- --------- ------------------- --------------
-------------------
A On Date  01/01/2002 08:00:00 Off Date  01/01/2002 11:00:00 Span (in
days)                .125
A On Date  01/01/2002 12:00:00 Off Date  02/01/2002 08:00:00 Span (in
days)          30.8333333
B On Date  01/01/2002 08:00:00 Off Date  01/01/2002 12:00:00 Span (in
days)          .166666667

Select a.obj, sum(Span)
From (Select a.obj, 'On Date ', a.thedate, 'Off Date ', b.thedate, 'Span (in days)', b.thedate - a.thedate Span
from bogus a, bogus b
where a.obj = b.obj

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

Group by a.obj;

O SUM(SPAN)
- ----------
A 30.9583333
B .166666667

This was fun!

Dale

gil guerillot wrote:

> here is one table who records all the changes of object status
>
> exemple
>
> OBJ status date
>
> A ON 01/01/02 8:00:00
> B ON 01/01/02 8:00:00
> C ON 01/01/02 10:00:00
> A OFF 01/01/02 11:00:00
> A ON 01/01/02 12:00:00
> B OFF 01/01/02 12:00:00
> D ON 01/01/02 13:00:00
> A OFF 02/01/02 8:00:00
>
> how can i do by a select query for calculate for each object the total time
> between ON and OFF
Received on Wed Feb 13 2002 - 11:11:32 CST