8
\$\begingroup\$

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 |
\$\endgroup\$
9
\$\begingroup\$

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 |
\$\endgroup\$
  • \$\begingroup\$ Nice thought. But it would fail on a record's date which actually resided within the span such as Start 3/8 and end 3/9. My bad for not adding that into the mix. I will update my post and SQLFiddle. \$\endgroup\$ – ΩmegaMan Oct 19 '14 at 22:08
  • \$\begingroup\$ @OmegaMan - revised my answer completely, better than before. \$\endgroup\$ – rolfl Oct 20 '14 at 0:02
  • \$\begingroup\$ Wow, ok you helped me on a SQL Server Sql I had and now on Oracle. You are multi-faceted developer. Oracle is not my forte and this issue felt like it could be modified and to which you proved. :-) I tested each of your revisions, settling on the final answer, and am able to process data for my Oracle client this time in a very efficient way. The previous question (for this actual data) was Remove Resultset Duplicates Based On Three Columns where that person taught me about Oracle Analytics. \$\endgroup\$ – ΩmegaMan Oct 20 '14 at 4:25
-2
\$\begingroup\$

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.

SQL query to find all students or events withing a time period when you only have start and end dates

\$\endgroup\$
  • 2
    \$\begingroup\$ Did you mean to paste to this thread for it is kinda related, but seems specific to something else... \$\endgroup\$ – ΩmegaMan Oct 23 '18 at 14:38
  • \$\begingroup\$ The fields I included are specific to a student being active in an academic program during a specific year, but it is the same issue that you had. You have a record that has a start and end date only, but you need to find any record which has a span of time withing a certain time period. \$\endgroup\$ – Lord Bobbymort Oct 23 '18 at 14:56
  • \$\begingroup\$ Welcome to Code Review! You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer. \$\endgroup\$ – Toby Speight Oct 23 '18 at 16:15
  • \$\begingroup\$ You have defined the problem, but it does not provide an answer in SQL code. \$\endgroup\$ – ΩmegaMan Oct 23 '18 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.