Home » SQL & PL/SQL » SQL & PL/SQL » date differences from one table
date differences from one table [message #189338] Thu, 24 August 2006 03:41 Go to next message
hershs
Messages: 5
Registered: August 2006
Junior Member
Hi,

I have table that handle items status history with dates of action:
CREATE TABLE DATESTEST
(
  OLDSTRING   VARCHAR2(20),
  NEWSTRING   VARCHAR2(20),
  CHANGEDATE  DATE,
  ITEMID      NUMBER
)

with data:
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Open', 'In Progress', TO_DATE('08/09/2006 11:16:07', 'MM/DD/YYYY HH24:MI:SS'), 18665);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('In Progress', 'Resolved', TO_DATE('08/09/2006 11:24:18', 'MM/DD/YYYY HH24:MI:SS'), 18665);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Resolved', 'Closed', TO_DATE('08/09/2006 11:24:25', 'MM/DD/YYYY HH24:MI:SS'), 18665);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Open', 'In Progress', TO_DATE('08/14/2006 14:55:02', 'MM/DD/YYYY HH24:MI:SS'), 18718);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('In Progress', 'Resolved', TO_DATE('08/14/2006 15:15:28', 'MM/DD/YYYY HH24:MI:SS'), 18718);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('In Progress', 'Resolved', TO_DATE('08/14/2006 15:16:38', 'MM/DD/YYYY HH24:MI:SS'), 18718);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Resolved', 'Closed', TO_DATE('08/14/2006 15:17:21', 'MM/DD/YYYY HH24:MI:SS'), 18718);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Resolved', 'In Progress', TO_DATE('08/14/2006 15:15:49', 'MM/DD/YYYY HH24:MI:SS'), 18718);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Open', 'In Progress', TO_DATE('08/15/2006 09:59:42', 'MM/DD/YYYY HH24:MI:SS'), 18820);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('In Progress', 'Resolved', TO_DATE('08/15/2006 10:01:58', 'MM/DD/YYYY HH24:MI:SS'), 18820);
Insert into DATESTEST
   (OLDSTRING, NEWSTRING, CHANGEDATE, ITEMID)
 Values
   ('Resolved', 'Closed', TO_DATE('08/15/2006 10:02:06', 'MM/DD/YYYY HH24:MI:SS'), 18820);

I need find the time that each item was in each state, for example:
SELECT * FROM DATESTEST WHERE ITEMID = 18718;
OLDSTRING       NEWSTRING     CHANGEDATE            ITEMID
-----           -----         -----                 -----
Open            In Progress   14-Aug-06 2:55:02 PM  18718
In Progress     Resolved      14-Aug-06 3:15:28 PM  18718
Resolved        In Progress   14-Aug-06 3:15:49 PM  18718
In Progress     Resolved      14-Aug-06 3:16:38 PM  18718
Resolved        Closed        14-Aug-06 3:17:21 PM  18718

I want to see how many time the item was in Open state , for this item - 20 minutes.

Can I do it by Oracle tools?

Thanks
Re: date differences from one table [message #189345 is a reply to message #189338] Thu, 24 August 2006 04:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thank you very much for posting CREATE and INSERT statements.
I'd have a stab at this for no other reason than that!

This should provide the info you want:
select itemid
      ,oldstring
      ,changedate
      ,newstring
      ,round((lead(changedate) over (partition by itemid order by changedate asc) - changedate) *24*60,2) mins
from   datestest;


Re: date differences from one table [message #189347 is a reply to message #189345] Thu, 24 August 2006 04:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This will provide a summary of the times:

select itemid,oldstring,newstring,sum(mins)
from   (
select itemid
      ,oldstring
      ,newstring
      ,round((lead(changedate) over (partition by itemid order by changedate asc) - changedate) *24*60,2) mins
from   datestest)
group by itemid,oldstring,newstring;
Re: date differences from one table [message #189348 is a reply to message #189338] Thu, 24 August 2006 04:24 Go to previous message
hershs
Messages: 5
Registered: August 2006
Junior Member
Thank you Smile
It's work!
Previous Topic: Export table as SQL query
Next Topic: Problem with recreate table with primary key constraint
Goto Forum:
  


Current Time: Fri Dec 09 07:42:44 CST 2016

Total time taken to generate the page: 0.21843 seconds