Home » SQL & PL/SQL » SQL & PL/SQL » Date difference with in rows on the same column using group by
icon1.gif  Date difference with in rows on the same column using group by [message #323201] Tue, 27 May 2008 13:12 Go to next message
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 #323206 is a reply to message #323201] Tue, 27 May 2008 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Where is the question?

I recommend you to read OraFAQ Forum Guide, especially... all sections.

Regards
Michel
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 Go to previous messageGo to next message
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 #323210 is a reply to message #323208] Tue, 27 May 2008 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't understand what you want.
You try to explain your problem with SQL expressions ("group by", "connect by") this is not the correct way. Explain it with words, what is your business need...

Post a test case: create table and insert statements.
Also post the result you want with these data.

Reread "How to format your post?" section.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Date difference with in rows on the same column using group by [message #323244 is a reply to message #323208] Tue, 27 May 2008 22:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You don't need a group by clause, since you are not using an aggregate function. What you need is a self join:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries006.htm#sthref3250

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066652
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 Go to previous messageGo to next message
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 #323816 is a reply to message #323810] Thu, 29 May 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Something like:
select EventID, Name, 
       sum(decode(type,'In',-1,1)*(EventTime-systimestamp)) diff
from EventLogStore
group by EventID, Name
/

Regards
Michel
Re: Date difference with in rows on the same column using group by [message #323824 is a reply to message #323816] Thu, 29 May 2008 10:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT a.eventid, a.name,
  2  	    b.eventtime - a.eventtime AS diff
  3  FROM   eventlogstore a, eventlogstore b
  4  WHERE  a.eventid = b.eventid
  5  AND    a.name = b.name
  6  AND    a.type = 'In'
  7  AND    b.type = 'Out'
  8  ORDER  BY eventid, name
  9  /

EVENTID NAME       DIFF
------- ---------- ------------------------------
a001    CheckAlarm +000000000 00:01:00.190000
a001    CheckLevel +000000000 00:03:59.983000
a002    CheckAlarm +000000000 00:01:00.190000

SCOTT@orcl_11g> 

Re: Date difference with in rows on the same column using group by [message #323836 is a reply to message #323824] Thu, 29 May 2008 12:07 Go to previous messageGo to next message
gk_dlk
Messages: 6
Registered: May 2008
Junior Member
Thanks Michel.

Barbara,
I appreciate your help, it's exactly what i am looking for. It looks like a simple solution.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Date difference with in rows on the same column using group by [message #323850 is a reply to message #323849] Thu, 29 May 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The core is EventTime(Out)-EventTime(In) ou 1*EventTime(Out) + (-1)*EventTime(In). This is the purpose of DECODE.

Now SUM only allows number datatype, so we can't sum interval or timestamp or date.
EventTime+0 is a trick to convert timestamp to date (so my remark on resolution).
Now here's another trick, to get number I subtract eventtime from sysdate, this gives me the interval (in days) between eventtime and current date. Given the coefficient +1/-1, on the SUM this sysdate part disappears:
1*(EventTime(out)-sysdate)+(-1)*(EventTime(in)-sysdate) = EventTime(out)-EventTime(in)

Regards
Michel
Re: Date difference with in rows on the same column using group by [message #323853 is a reply to message #323850] Thu, 29 May 2008 14:47 Go to previous messageGo to next message
gk_dlk
Messages: 6
Registered: May 2008
Junior Member
Great.It looks like a smart way of doing.

SQL Query to display the time difference in minutes is

select EventID, Name, sum(decode(type,'In',-1,1)*((EventTime+0-sysdate)*(24*60))) diff from EventLogStore group by EventID, Name

Thanks.
Re: Date difference with in rows on the same column using group by [message #323913 is a reply to message #323853] Fri, 30 May 2008 00:46 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, take care with this you loose subsecond part. I don't know if it is important for you.
Check also performances, depending on data and indexes Barbara's solution may be the fastest one.

Regards
Michel

[Updated on: Fri, 30 May 2008 00:47]

Report message to a moderator

Previous Topic: Execute any sql statement in package and return cursor
Next Topic: Error in email
Goto Forum:
  


Current Time: Sun Feb 16 05:30:38 CST 2025