Home » SQL & PL/SQL » SQL & PL/SQL » Date difference with in rows on the same column using group by
Date difference with in rows on the same column using group by [message #323201] |
Tue, 27 May 2008 13:12  |
gk_dlk
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
I have following data in this database table 'EventLog'
EventID NAME TYPE EventTime
a001 CheckLevel In 2008-05-26 10:01:00.120
a001 CheckLevel Out 2008-05-26 10:05:00.103
a001 CheckAlaram In 2008-05-26 10:06:00.110
a001 CheckAlaram Out 2008-05-26 10:07:00.30
a001 CheckTeam In 2008-05-26 11:08:00.111
a001 CheckTeam Out 2008-05-26 11:09:00.321
a012 CheckLevel In 2008-05-27 10:01:00.120
a012 CheckLevel Out 2008-05-27 10:05:00.103
a012 CheckAlaram In 2008-05-27 10:06:00.110
a012 CheckAlaram Out 2008-05-27 10:07:00.30
I need to write a SQL query to select group by EventID with this projected data
a001 CheckLevel (2008-05-26 10:05:00.103)-(2008-05-26 10:01:00.120)
a001 CheckAlaram (2008-05-26 10:07:00.300)-(2008-05-26 10:06:00.110)
a001 CheckTeam (2008-05-26 11:09:00.321)-(2008-05-26 11:08:00.111)
a012 CheckLevel (2008-05-27 10:05:00.103)-(2008-05-27 10:01:00.120)
a012 CheckAlaram (2008-05-27 10:07:00.30)-(2008-05-27 10:06:00.110)
I also attahced this snapshot.
Your help would be greatly appreciated.
[mod-edit: added code tags]
-
Attachment: EventLog.JPG
(Size: 75.04KB, Downloaded 423 times)
[Updated on: Tue, 27 May 2008 21:54] by Moderator Report message to a moderator
|
|
|
|
Re: Date difference with in rows on the same column using group by [message #323208 is a reply to message #323206] |
Tue, 27 May 2008 14:35   |
gk_dlk
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
When I checked in the preview section before posting it, It didn't format my posting correctly. It may be because of my browser issue or may be a issue in posting with spaces. That was the reason I attached the snapshot of my question.
It's my first time to write this kind of SQL Query. I don't know whether it's related to 'Connect by' clause. I am looking for some help to start digging it. If you can help me that would be much appreciated.Please let me know if it's not cleared to you still.
I have two records for particular eventid and name in the eventlog table. Event types for those records are 'In' and 'out' respectively. Record type 'In' will have event time that will be always less than the event time of record type 'out'.
How to start writing a query for selecting the data with the date difference between those two records of the column'eventtime' for a particular combination of eventid and name?
Please suggest me if you get any idea.your help is much appreciated.
[Updated on: Tue, 27 May 2008 14:48] Report message to a moderator
|
|
|
|
|
Re: Date difference with in rows on the same column using group by [message #323810 is a reply to message #323244] |
Thu, 29 May 2008 10:07   |
gk_dlk
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
Thanks Barbara. I'm trying to build it.
Michel,
I'm seeking a help in this forum to start writing a query or a solution for my problem, i don't want to confuse you or don't like to complicate the problem.
Here is the description of the problem:
my table and insert statements are given below:
CREATE TABLE EventLogStore(EventID VARCHAR2(128),Name VARCHAR2(128),Type VARCHAR2(20),EventTime TIMESTAMP(6));
Insert into EventLogStore(EventID,Name,Type,EventTime) values ('a001','CheckLevel','In',TO_TIMESTAMP('2008-05-26 10:01:00.120','YYYY-MM-DD HH24:MI:SS.FF'));
Insert into EventLogStore(EventID,Name,Type,EventTime) values ('a001','CheckLevel','Out',TO_TIMESTAMP('2008-05-26 10:05:00.103','YYYY-MM-DD HH24:MI:SS.FF'));
Insert into EventLogStore(EventID,NAME,TYPE,EventTime) Values('a001','CheckAlaram','In',TO_TIMESTAMP('2008-05-26 10:06:00.110','YYYY-MM-DD HH24:MI:SS.FF'));
Insert into EventLogStore(EventID,NAME,TYPE,EventTime) Values('a001','CheckAlaram','Out',TO_TIMESTAMP('2008-05-26 10:07:00.30','YYYY-MM-DD HH24:MI:SS.FF'));
Insert into EventLogStore(EventID,NAME,TYPE,EventTime) Values('a002','CheckAlaram','In',TO_TIMESTAMP('2008-05-26 11:06:00.110','YYYY-MM-DD HH24:MI:SS.FF')) ;
Insert into EventLogStore(EventID,NAME,TYPE,EventTime) Values('a002','CheckAlaram','Out',TO_TIMESTAMP('2008-05-26 11:07:00.30','YYYY-MM-DD HH24:MI:SS.FF'));
Combination of EventID, Name and Type is unique. Combination of eventID and name will always have two records, one record will have the value 'In' for the column 'Type' and the other record will have 'Out' value for the column 'Type', these records have eventtime as timestamp, I need to display the difference between the timestamp on these records for each and every EventID and Name.
It much appreciated If you can help solving it.Please let me know if you don't understand still.
|
|
|
|
|
|
Re: Date difference with in rows on the same column using group by [message #323842 is a reply to message #323816] |
Thu, 29 May 2008 12:58   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
More precisely, diff in unit of day:
SQL> select EventID, Name,
2 sum(decode(type,'In',-1,1)*(EventTime+0-sysdate)) diff
3 from EventLogStore
4 group by EventID, Name
5 /
EVENT NAME DIFF
----- ------------ ----------
a002 CheckAlaram .000694444
a001 CheckLevel .002777778
a001 CheckAlaram .000694444
3 rows selected.
This avoid to access twice the table but has a resolution of 1 second.
Regards
Michel
|
|
|
Re: Date difference with in rows on the same column using group by [message #323849 is a reply to message #323842] |
Thu, 29 May 2008 13:51   |
gk_dlk
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
Thanks for your help. I think your solution is returning different result. I didn't understand this logic in this expression 'sum(decode(type,'In',-1,1)*(EventTime+0-sysdate))'.
I modified Barbara suggested solution to convert the time difference in to nanoseconds.
SELECT a.eventid, a.name,(b.eventtime - a.eventtime)*24*60*60*1000 AS diff FROM eventlogstore a, eventlogstore b WHERE a.eventid = b.eventid AND a.name = b.name AND a.type = 'In' AND b.type = 'Out' ORDER BY eventid, name
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 05:30:38 CST 2025
|