Filter out date records which don't fall within or span a date range

Question

I have records which have specific time spans. The need is to select records which adhere to the following three rules (read them as or conditions) concerning a target time span:

1. Record's Begin date can begin within the target time span and end date does not matter.
2. Record's End date falls with the target time span and the start date is inconsequential.
3. Record's dates actually span the target time.

So basically a query should return all records which start, end or span a target time frame.

Is there a more efficient way in Oracle 11g of doing the above rules than what I currently have stated in the SQL below?

---

I have created a test SQL Fiddle Test for the below schema and SQL

Schema

CREATE TABLE dates_test (
  id         NUMBER,
  hint       VarChar(20),
  BEGIN_DATE DATE,
  END_DATE DATE
);
-- If the span is Mar 6 to April 6 then 2, 3 and 4 are valid -
INSERT INTO dates_test VALUES (1, 'exclude before', TO_DATE('01-JAN-2014','DD-MON-YYYY'), TO_DATE('05-JAN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 'spans',          TO_DATE('05-MAR-2014','DD-MON-YYYY'), TO_DATE('08-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (3, 'Starts within',  TO_DATE('07-MAR-2014','DD-MON-YYYY'), TO_DATE('07-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (4, 'Ends within',    TO_DATE('01-JAN-2014','DD-MON-YYYY'), TO_DATE('07-MAR-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (5, 'exclude after',  TO_DATE('06-JUN-2014','DD-MON-YYYY'), TO_DATE('07-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (6, 'within OK',  TO_DATE('08-MAR-2014','DD-MON-YYYY'), TO_DATE('09-MAR-2014','DD-MON-YYYY'));

Here is the SQL which returns what I need but can it be improved?

select * from
dates_test t0
where 
(  -- Starts Within range
  ( t0.BEGIN_DATE 
    BETWEEN TO_DATE('03-06-2014', 'mm-dd-yyyy') 
    AND     TO_DATE('04-06-2014', 'mm-dd-yyyy')
  )
OR  -- Ends within range
  (
    t0.END_DATE
    BETWEEN TO_DATE('03-06-2014', 'mm-dd-yyyy') 
    AND     TO_DATE('04-06-2014', 'mm-dd-yyyy')    
   )
OR    -- SPANS Range
   (
     t0.BEGIN_DATE < TO_DATE('03-06-2014', 'mm-dd-yyyy')
     AND
     t0.END_DATE > TO_DATE('04-06-2014', 'mm-dd-yyyy')   
    )
);

Result:

| ID |          HINT |                     BEGIN_DATE |                         END_DATE |
|----|---------------|--------------------------------|----------------------------------|
|  2 |         spans |   March, 05 2014 00:00:00+0000 | September, 08 2014 00:00:00+0000 |
|  3 | Starts within |   March, 07 2014 00:00:00+0000 | September, 07 2014 00:00:00+0000 |
|  4 |   Ends within | January, 01 2014 00:00:00+0000 |     March, 07 2014 00:00:00+0000 |
|  5 |     within OK |   March, 08 2014 00:00:00+0000 |     March, 09 2014 00:00:00+0000 |

Answer 1

So, My first answer messed up (Can I blame it on incomplete test data?)

That's a good thing to note, that your should design your tests to cover the situations that are significant to you. Because your tests were incomplete, when I ran and modified your code, and got the 'same' results, I assumed the code was good. I never validated your test cases (my fault).

This re-work made me realize that your data would be better served with a negative test case. What you want to test for is exclusionary ranges. If you can assume your data is valid (it is, right), then you want to just test those cases where there is no intersection.

There is no intersection if the test date ends before the data's date, or the data's date ends before the test date. This simplifies the query to:

select *
from dates_test t0
where not (
      END_DATE < TO_DATE('03-06-2014', 'mm-dd-yyyy')
   or BEGIN_DATE > TO_DATE('04-06-2014', 'mm-dd-yyyy')

As was pointed out to me (very nicely, I may add), the above logic can be simplified further using 'basic' boolean logic to:

select *
from dates_test t0
where END_DATE >= TO_DATE('03-06-2014', 'mm-dd-yyyy')
  and BEGIN_DATE <= TO_DATE('04-06-2014', 'mm-dd-yyyy'))

which is my final answer (thanks @mjolka)

Using your (revised) SQL fiddle, the above query produces (note that your revised data has two records with id 5):

| ID |          HINT |                     BEGIN_DATE |                         END_DATE |
|----|---------------|--------------------------------|----------------------------------|
|  2 |         spans |   March, 05 2014 00:00:00+0000 | September, 08 2014 00:00:00+0000 |
|  3 | Starts within |   March, 07 2014 00:00:00+0000 | September, 07 2014 00:00:00+0000 |
|  4 |   Ends within | January, 01 2014 00:00:00+0000 |     March, 07 2014 00:00:00+0000 |
|  5 |     within OK |   March, 08 2014 00:00:00+0000 |     March, 09 2014 00:00:00+0000 |

Answer 2

EDIT: I've squeezed the filter a lot. I couldn't wrap my head around it before how to make sure something really fit within the time period. It's this: Start date BEFORE the END of the time period, and End date AFTER the BEGINNING of the time period

With the help of someone in my office I think we've figured out how to include everyone in the filter. There are 5 scenarios where a student would be deemed active during the time period in question:

1) Student started and ended during the time period.

2) Student started before and ended during the time period.

3) Student started before and ended after the time period.

4) Student started during the time period and ended after the time period.

5) Student started during the time period and is still active (Doesn't have an end date yet)

Given these criteria, we can actually condense the statements into a few groups because a student can only end between the period dates, after the period date, or they don't have an end date:

1) Student ends during the time period AND [Student starts before OR during]

2) Student ends after the time period AND [Student starts before OR during]

3) Student hasn't finished yet AND [Student starts before OR during]

   (
        (
         student_programs.END_DATE  >=  '07/01/2017 00:0:0'
         OR
         student_programs.END_DATE  Is Null  
        )
        AND
        student_programs.START_DATE  <=  '06/30/2018 23:59:59'
   )

I think this finally covers all the bases and includes all scenarios where a student, or event, or anything is active during a time period when you only have start date and end date. I've attached a screenshot of the SAP Business Objects query as well. Please, do not hesitate to tell me that I am missing something. I want this to be perfect so others can use this, as I don't believe the other answers have gotten everything right yet.