Home » SQL & PL/SQL » SQL & PL/SQL » [Help] Partially overlapped time calculation (Oracle plsql)
[Help] Partially overlapped time calculation [message #402061] Thu, 07 May 2009 07:58 Go to next message
Kimsan
Messages: 1
Registered: May 2009
Junior Member
I have an table containing following dates. Which shows when Mac Id 10 has been in use.

Start time End Time Mac Id
------------------------------------------------------
2009-05-06 08:00:00 2009-05-06 11:00:00 10
2009-05-06 08:00:00 2009-05-06 11:00:00 10
2009-05-06 08:00:00 2009-05-06 14:00:00 10
2009-05-06 09:00:00 2009-05-06 12:00:00 10
2009-05-06 09:00:00 2009-05-06 10:00:00 10
2009-05-06 13:00:00 2009-05-06 14:00:00 10
2009-05-06 15:00:00 2009-05-06 16:00:00 10

When looking at it I see that mac id 10 has been in use for
08:00-14:00 (6 hours) and 15:00-16:00 (1 hour) = 7 hours

Is it possible to calculate this in an query? If so how?


I would like it to return:
Start Time End Time Mac Id
------------------------------------------------------
2009-05-06 08:00:00 2009-05-06 14:00:00 10
2009-05-06 15:00:00 2009-05-06 16:00:00 10


Regads,
Re: [Help] Partially overlapped time calculation [message #402064 is a reply to message #402061] Thu, 07 May 2009 08:11 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and search for "Analytics to the Rescue (Again) "

http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html

http://www.orafaq.com/forum/m/384398/94420/?srch=sliding+window#msg_384398

From next time if you could format your post and follow the guidelines, it will be much appreciated.

Regards

Raj

[Edit: ] Added another url

[Updated on: Thu, 07 May 2009 08:14]

Report message to a moderator

Previous Topic: Converting xmltype to varchar2(merged)
Next Topic: Random Selections
Goto Forum:
  


Current Time: Sat Dec 10 05:00:23 CST 2016

Total time taken to generate the page: 0.16791 seconds