CODEWITHSUNDEEP
sqloracle-databasedate
MLPNPC
MLPNPC

Reputation: 525

Check if a week of dates sits between two specific dates SQL ORACLE

I have two dates and want to only keep rows where a certain week falls in between those two dates. I need to run this for 67+ different weeks so I'd like to change my below code to run quicker and not have to type out the 7 days of the week each time. 

SELECT * FROM MY_DATA
WHERE  ('05-JUNE-2017' BETWEEN start_date AND end_date
OR      '06-JUNE-2017' BETWEEN start_date AND end_date
OR      '07-JUNE-2017' BETWEEN start_date AND end_date
OR      '08-JUNE-2017' BETWEEN start_date AND end_date
OR      '09-JUNE-2017' BETWEEN start_date AND end_date
OR      '10-JUNE-2017' BETWEEN start_date AND end_date
OR      '11-JUNE-2017' BETWEEN start_date AND end_date);

Upvotes: 0

Views: 188

Answers (3)

Radagast81
Radagast81

Reputation: 3016

I think you are looking for something like the following:

WITH weeks AS (SELECT TO_DATE('05-07-2017', 'DD-MM-RRRR') + LEVEL * 7 - 7 week_start
                    , TO_DATE('05-07-2017', 'DD-MM-RRRR') + LEVEL * 7 - 1 week_end
                 FROM dual
                 CONNECT BY LEVEL < 50)
SELECT * 
  FROM MY_DATA
  JOIN WEEKS
    ON MY_DATA.end_date   >= weeks.week_start
   AND MY_DATA.start_date <= weeks.week_end

The first part gets you some consecutive weeks then you join them with your data to get the desired results.

Upvotes: 0

xavier
xavier

Reputation: 2049

I think your query is equivalent to 

SELECT * FROM MY_DATA WHERE  ('05-JUNE-2017' BETWEEN start_date - 6 AND end_date );

where '05-JUNE-2017' is the initial day of the week you are looking for.

Now you have to do this for all the initial week days...

Is that what you are looking for?

Upvotes: 0

user5683823
user5683823

Reputation:

So, you have two date intervals. One is from input_date to input_date + 6 (note: 6, not 7; you are counting the input_date too!) and the other is from start_date to end_date. And you want to write a condition that means the two intervals overlap (have non-empty intersection).

Two intervals do not overlap if one entirely precedes the other. So, the last day of one interval must be strictly less than the first day of the other interval. This can happen in one of two ways (depending on which interval comes first). The condition would look like this:

input_date + 6 < start_date OR end_date < input_date

So, this is the negation of the condition you want. Apply the logical NOT operator to this; so the condition becomes:

input_date + 6 >= start_date AND end_date >= input_date

Upvotes: 4

Related Questions