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:
- Record's Begin date can begin within the target time span and end date does not matter.
- Record's End date falls with the target time span and the start date is inconsequential.
- 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 |