Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

More than one record in the same interval 1

Status
Not open for further replies.
AnaIsabelFlor77

AnaIsabelFlor77

Programmer
Joined
Mar 17, 2007
Messages
12
Location
PT
Hello,

I have a table that defines wich person is using a DB user at a time:

DB_USER ID_NAME LOGIN LOGOFF
USER1 Ana 27-10-2012 18:10:05 27-10-2012 18:18:05
USER1 John 27-10-2012 18:18:06 27-10-2012 18:28:05
USER1 Greg 27-10-2012 18:20:09 27-10-2012 18:25:05


How can I find out if more than one person was using the same DB user at that interval of time? I know that in this example it should return the following:

DB_USER ID_NAME Date
USER1 John 27-10-2012
USER1 Greg 27-10-2012

Can you help me?
Thanks
 
Sort by date Sort by votes
If you have workspace manager available to you check out the WM_PERIOD and WM_OVERAPS - and a few other WM_*
funtions too - which will help you solve this. Alternatively there is an undocumented Oracle SQL function called overlaps which again may be of use, although I wouldn't base any production tasks on it.


In order to understand recursion, you must first understand recursion.
 
Upvote 0 Downvote
Ana,

Given your data:

Code: 
select A.DB_USER a_db_user
      ,A.ID_NAME a_id_name
      ,to_char(A.LOGIN,'dd-MON-yyyy hh24:mi:ss') a_login
      ,to_char(A.LOGOFF,'dd-MON-yyyy hh24:mi:ss') a_logoff
  from logins a
/

A_DB_USER A_ID_NAME A_LOGIN              A_LOGOFF
--------- --------- -------------------- --------------------
USER1     Ana       27-OCT-2012 18:10:05 27-OCT-2012 18:18:05
USER1     John      27-OCT-2012 18:18:06 27-OCT-2012 18:28:05
USER1     Greg      27-OCT-2012 18:20:09 27-OCT-2012 18:25:05

3 rows selected.

...there are several ways you can solve your need. Here is one way:

Code: 
SELECT A.DB_USER a_db_user
      ,A.ID_NAME a_id_name
      ,to_char(A.LOGIN,'dd-MON-yyyy hh24:mi:ss') a_login
      ,to_char(A.LOGOFF,'dd-MON-yyyy hh24:mi:ss') a_logoff
      ,b.DB_USER b_db_user
      ,b.ID_NAME b_id_name
      ,to_char(b.LOGIN,'dd-MON-yyyy hh24:mi:ss')  b_login
      ,to_char(b.LOGOFF,'dd-MON-yyyy hh24:mi:ss') b_logoff
  from (SELECT DB_USER, ID_NAME, LOGIN, LOGOFF
          FROM LOGINS) A
      ,(SELECT DB_USER, ID_NAME, LOGIN, LOGOFF
          FROM LOGINS) b
 WHERE a.db_user = b.db_user
   and a.id_name <> b.id_name
   and (a.login between b.login and b.logoff
        or
        a.logoff between b.login and b.logoff)
/

A_DB_USER A_ID_NAME A_LOGIN              A_LOGOFF             B_DB_USER B_ID_NAME B_LOGIN              B_LOGOFF
--------- --------- -------------------- -------------------- --------- --------- -------------------- --------------------
USER1     Greg      27-OCT-2012 18:20:09 27-OCT-2012 18:25:05 USER1     John      27-OCT-2012 18:18:06 27-OCT-2012 18:28:05

1 row selected.

Let us know if this is satisfactory and if you have additional questions.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rk68
  • Locked
  • Question Question
Data encryption in Oracle 19c
Replies
0
Views
543
rk68
rk68
Livia8
  • Locked
  • Question Question
SQL SELECT - where date is null or greater than
Replies
9
Views
7K
carp
carp
George L
  • Locked
  • Question Question
web app not saving &quot;all&quot; records suddenly
Replies
3
Views
1K
carp
carp
Andrzejek
  • Locked
  • Question Question
Insert multiple records
Replies
9
Views
2K
mikrom
mikrom
torturedmind
  • Locked
  • Question Question
Week of the year
Replies
0
Views
414
torturedmind
torturedmind

Part and Inventory Search

Sponsor

Back
Top