Home » SQL & PL/SQL » SQL & PL/SQL » Determining dates on which events (some overlapping) did not occur
Determining dates on which events (some overlapping) did not occur [message #314465] Wed, 16 April 2008 16:16 Go to next message
tracy17
Messages: 8
Registered: March 2005
Junior Member
I have data like the following:

Person=1
Entry date= 1/1/2000
Exit date= 1/20/2000
Event 1, Occurrence 1: start 1/5/2000, end 1/7/2000
Event 1, Occurrence 2: start 1/7/2000, end 1/15/2000
Event 2, Occurrence 1: start 1/13/2000, end 1/18/2000

Person=2
Entry date= 2/1/2000
Exit date= 2/28/2000
Event 1, Occurrence 1: start 2/2/2000, end 2/18/2000
Event 2, Occurrence 1: start 2/20/2000, end 2/24/2000
Event 3, Occurrence 1: start 2/22/2000, end 2/27/2000

I want to determine the # calendar days in which each person was "present" (where present = between entry date and exit date) but did not have any "events." In the ex. above, person 1 would have 6 such days (1/1/2000-1/4/2000 and 1/19/2000-1/20/2000), while person 2 would have 3 such days (2/1/2000, 2/19/2000, and 2/28/2000).

I would like to do this in SQL rather than PL/SQL. Is this possible? Any advice would be much appreciated.

Thanks in advance.

Re: Determining dates on which events (some overlapping) did not occur [message #314477 is a reply to message #314465] Wed, 16 April 2008 18:40 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Please read the forum guidelines:

http://www.orafaq.com/forum/t/88153/0/

then either edit your post or post a reply to it with the missing information, such as Oracle version, create table and insert statements for sample data, a copy and paste of some attempted query of your own using indentation and code tags, including the results and/or error messages that your query produces from that data, and the results that you want from that data.
Previous Topic: Logic trouble
Next Topic: Set column size
Goto Forum:
  


Current Time: Fri Dec 09 17:36:02 CST 2016

Total time taken to generate the page: 0.18338 seconds