ORACLE SQL Date range intersections
here is a quick solution (may not be the most efficient):
SQL> CREATE TABLE myData AS
2 SELECT 'A' name, date'2010-01-01' d1, date'2010-12-11' d2 FROM DUAL
3 UNION ALL SELECT 'B', date'2010-01-20', date'2010-04-15' FROM DUAL
4 UNION ALL SELECT 'B', date'2010-05-10', date'2010-12-30' FROM DUAL
5 UNION ALL SELECT 'C', date'2010-03-13', date'2010-06-10' FROM DUAL;
Table created
SQL> WITH segments AS (
2 SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
3 FROM (SELECT d1 dat FROM myData
4 UNION
5 SELECT d2 dat FROM myData)
6 )
7 SELECT s.seg_low, s.seg_high
8 FROM segments s
9 JOIN myData m ON s.seg_high > m.d1
10 AND s.seg_low < m.d2
11 GROUP BY s.seg_low, s.seg_high
12 HAVING COUNT(DISTINCT NAME) = 3;
SEG_LOW SEG_HIGH
----------- -----------
13/03/2010 15/04/2010
10/05/2010 10/06/2010
I build all the possible successive date ranges and join this "calendar" with the sample data. This will list all ranges that have 3 values. You may need to merge the result if you add rows:
SQL> insert into mydata values ('B',date'2010-04-15',date'2010-04-16');
1 row inserted
SQL> WITH segments AS (
2 SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
3 FROM (SELECT d1 dat FROM myData
4 UNION
5 SELECT d2 dat FROM myData)
6 )
7 SELECT MIN(seg_low), MAX(seg_high)
8 FROM (SELECT seg_low, seg_high, SUM(gap) over(ORDER BY seg_low) grp
9 FROM (SELECT s.seg_low, s.seg_high,
10 CASE
11 WHEN s.seg_low
12 = lag(s.seg_high) over(ORDER BY s.seg_low)
13 THEN 0
14 ELSE 1
15 END gap
16 FROM segments s
17 JOIN myData m ON s.seg_high > m.d1
18 AND s.seg_low < m.d2
19 GROUP BY s.seg_low, s.seg_high
20 HAVING COUNT(DISTINCT NAME) = 3))
21 GROUP BY grp;
MIN(SEG_LOW) MAX(SEG_HIGH)
------------ -------------
13/03/2010 16/04/2010
10/05/2010 10/06/2010
find overlap between two sets of date ranges
It seems what you are looking for is not INTERSECT but overlaps. In Oracle intersect generally refers to the common result of 2 queries:
Select <columns list> from table1
INTERSECT
Select <columns list> from table2;
Where the column lists have the same definition and the resulting values are the same. What you are looking for is where the values overlap one another each other not where the rows contain same values.
Lets consider 2 events call then 'A' and 'B', there are 4 possibilities for overlap:
- A starts, B starts, B ends, A ends. A completely overlaps B.
- A starts, B starts, A ends, B ends. A overlaps beginning of B
- B starts, A starts, B ends, A ends. A overlaps ending of B
- B starts, A starts, A ends, B ends. A is completely overlap by B.
Resolving is just determining is needs to determine the overlap we take the greatest start time and the least end time. With the data you provided this requires just one of the above:
select order_number
, greatest(t1start, t2start) start_date_time
, least(t1finish,t2finish) finish_date_time
from ( select t1.order_number
, t1.start_date_time t1start
, t1.finish_date_time t1finish
, t2.start_date_time t2start
, t2.finish_date_time t2finish
from t_result_set_one t1
join t_result_set_two t2
on t1.order_number = t2.order_number
where ( t1.finish_date_time >= t2.start_date_time
and t1.start_date_time <= t2.finish_date_time
)
);
See fiddle here. I leave the other 3 possibilities for you.
Determine Whether Two Date Ranges Overlap
(StartA <= EndB) and (EndA >= StartB)
Proof:
Let ConditionA Mean that DateRange A Completely After DateRange B
_ |---- DateRange A ------|
|---Date Range B -----| _
(True if StartA > EndB)
Let ConditionB Mean that DateRange A is Completely Before DateRange B
|---- DateRange A -----| _
_ |---Date Range B ----|
(True if EndA < StartB)
Then Overlap exists if Neither A Nor B is true -
(If one range is neither completely after the other,
nor completely before the other,
then they must overlap.)
Now one of De Morgan's laws says that:
Not (A Or B) <=> Not A And Not B
Which translates to: (StartA <= EndB) and (EndA >= StartB)
NOTE: This includes conditions where the edges overlap exactly. If you wish to exclude that,
change the >= operators to >, and <= to <
NOTE2. Thanks to @Baodad, see this blog, the actual overlap is least of:
{ endA-startA, endA - startB, endB-startA, endB - startB }
(StartA <= EndB) and (EndA >= StartB)(StartA <= EndB) and (StartB <= EndA)
NOTE3. Thanks to @tomosius, a shorter version reads:DateRangesOverlap = max(start1, start2) < min(end1, end2)
This is actually a syntactical shortcut for what is a longer implementation, which includes extra checks to verify that the start dates are on or before the endDates. Deriving this from above:
If start and end dates can be out of order, i.e., if it is possible that startA > endA or startB > endB, then you also have to check that they are in order, so that means you have to add two additional validity rules:(StartA <= EndB) and (StartB <= EndA) and (StartA <= EndA) and (StartB <= EndB)
or:(StartA <= EndB) and (StartA <= EndA) and (StartB <= EndA) and (StartB <= EndB)
or,(StartA <= Min(EndA, EndB) and (StartB <= Min(EndA, EndB))
or:(Max(StartA, StartB) <= Min(EndA, EndB)
But to implement Min() and Max(), you have to code, (using C ternary for terseness),:((StartA > StartB) ? StartA : StartB) <= ((EndA < EndB) ? EndA : EndB)
Merge Datetime Ranges Oracle SQL or PL/SQL
This is adapted from this answer which contains an explanation of the code. All that has changed is to add PARTITION BY order_id to calculate the date ranges for each order_id and then to return the ranges (rather than total the values, as per the linked answer):
SELECT order_id,
start_date_time,
end_date_time
FROM (
SELECT order_id,
LAG( dt ) OVER ( PARTITION BY order_id ORDER BY dt ) AS start_date_time,
dt AS end_date_time,
start_end
FROM (
SELECT order_id,
dt,
CASE SUM( value ) OVER ( PARTITION BY order_id ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM table_name
UNPIVOT ( dt FOR value IN ( start_date_time AS 1, end_date_time AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end';
From Oracle 12, you can use MATCH_RECONIZE to do row-by-row processing:
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY order_id
ORDER BY start_date_time
MEASURES
FIRST(start_date_time) AS start_date_time,
MAX(end_date_time) AS end_date_time
ONE ROW PER MATCH
PATTERN (overlapping_rows* last_row)
DEFINE
overlapping_rows AS NEXT(start_date_time) <= MAX(end_date_time)
)
Which, for your test data:
CREATE TABLE table_name (
order_id NUMBER,
start_date_time DATE,
end_date_time DATE
);
INSERT INTO table_name ( order_id, start_date_time, end_date_time )
SELECT 3933, TIMESTAMP '2020-02-04 08:00:00', TIMESTAMP '2020-02-04 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 13:30:00', TIMESTAMP '2020-02-04 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 14:00:00', TIMESTAMP '2020-02-04 19:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 13:40:12', TIMESTAMP '2020-02-05 14:34:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:00:00', TIMESTAMP '2020-02-05 18:55:12' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:49:48', TIMESTAMP '2020-02-05 15:04:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 08:00:00', TIMESTAMP '2020-02-06 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 13:30:00', TIMESTAMP '2020-02-06 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 14:10:12', TIMESTAMP '2020-02-06 18:49:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 10:30:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 13:30:00', TIMESTAMP '2020-02-07 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 15:00:00', TIMESTAMP '2020-05-14 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 08:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 09:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 10:15:00', TIMESTAMP '2020-05-15 12:15:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 13:19:48', TIMESTAMP '2020-05-15 16:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 08:49:48', TIMESTAMP '2020-05-18 09:45:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 16:58:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 15:34:48', TIMESTAMP '2020-05-18 16:10:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 16:30:00', TIMESTAMP '2020-05-18 16:45:00' FROM DUAL;
Which both output:
ORDER_ID | START_DATE_TIME | END_DATE_TIME
-------: | :------------------ | :------------------
3933 | 2020-02-04 08:00:00 | 2020-02-04 12:00:00
3933 | 2020-02-04 13:30:00 | 2020-02-04 19:00:00
3933 | 2020-02-05 13:40:12 | 2020-02-05 18:55:12
3933 | 2020-02-06 08:00:00 | 2020-02-06 12:00:00
3933 | 2020-02-06 13:30:00 | 2020-02-06 18:49:48
3933 | 2020-02-07 08:00:00 | 2020-02-07 12:00:00
3933 | 2020-02-07 13:30:00 | 2020-02-07 17:00:00
11919 | 2020-05-14 09:00:00 | 2020-05-14 17:00:00
11919 | 2020-05-15 08:40:12 | 2020-05-15 16:30:00
11919 | 2020-05-18 08:49:48 | 2020-05-18 09:45:00
11919 | 2020-05-18 10:00:00 | 2020-05-18 17:00:00
db<>fiddle here
Find records with overlapping date range in SQL
Try this:
select * from t t1
join t t2 on (t1.datefrom > t2.datefrom and t1.datefrom < t2.dateto)
or (t1.dateto > t2.datefrom and t1.dateto < t2.dateto)
Thank You for this example. After modification it is working:
SELECT *
FROM customer_wer k
JOIN customer_wer w
ON k.id_customer = w.id_customer
WHERE (k.date_from > w.date_to AND k.date_from < w.date_to)
OR (k.date_to > w.date_from AND k.date_to < w.date_to);
Detect overlapping date ranges from the same table
If you already have entries for each day that should work, but if you don't the overhead is significant, and if that query is used often, if will affect performance.
If the data is in this format, you can detect overlaps using simple date arithmetic, because an overlap is simply one interval starting after a given interval, but before the given is finished, something like
select dr1.* from date_ranges dr1
inner join date_ranges dr2
on dr2.start > dr1.start -- start after dr1 is started
and dr2.start < dr1.end -- start before dr1 is finished
If you need special handling for interval that are wholly within another interval, or you need to merge intervals, i.e.
PKey Start End Type
==== ===== === ====
01 01/01/2010 20/01/2010 S
02 15/01/2010 31/01/2010 S
yielding
Start End Type
===== === ====
01/01/2010 31/01/2010 S
you will need more complex calculation.
In my experience with this kind of problems, once you get how to do the calculation by hand, it's easy to transfer it into SQL :)
Check if current date is between two dates Oracle SQL
You don't need to apply to_date() to sysdate. It is already there:
select 1
from dual
WHERE sysdate BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND TO_DATE('20/06/2014', 'DD/MM/YYYY');
If you are concerned about the time component on the date, then use trunc():
select 1
from dual
WHERE trunc(sysdate) BETWEEN TO_DATE('28/02/2014', 'DD/MM/YYYY') AND
TO_DATE('20/06/2014', 'DD/MM/YYYY');
How to get monthly periods between a date range in Oracle PL/SQL
Assuming the interval is given by way of two bind variables, :from_dt and :to_dt (strings in the indicated format):
with
inputs ( f_dt, t_dt ) as (
select to_date(:from_dt, 'mm/dd/yyyy'), to_date(:to_dt, 'mm/dd/yyyy') from dual
),
ld ( l_day, lvl ) as (
select add_months(last_day(f_dt), level - 1), level
from inputs
connect by level <= months_between(last_day(t_dt), last_day(f_dt)) + 1
)
select case when ld.lvl = 1 then i.f_dt else add_months(ld.l_day, -1) + 1 end
as start_date,
least(i.t_dt, ld.l_day) as end_date
from inputs i cross join ld
;
This assumes that in the original post you did, in fact, mean to have one more interval, from 3/1/2002 to 3/1/2002; and the query deals correctly with the case when the from-date and the to-date are in the same month: if the inputs are 6/11/1996 to 6/21/1996, then the output is exactly that interval.
Added: creating column aliases in the declaration of factored subqueries (in the WITH clause), as I have done, requires Oracle 11.2 or above. For earlier versions, it is necessary to write it a little differently, like so:
with
inputs as (
select to_date(:from_dt, 'mm/dd/yyyy') as f_dt,
to_date(:to_dt , 'mm/dd/yyyy') as t_dt
from dual
),
ld as (
select add_months(last_day(f_dt), level - 1) as l_day, level as lvl
from inputs ...............
Related Topics
Recursive Query Challenge - Simple Parent/Child Example
How to Set List of Values as Parameter into Hibernate Query
Using a SQL Server for Application Logging. Pros/Cons
Insert-Output Including Column from Other Table
Why Is My Left Join Not Returning Nulls
How to Use T-SQL Full-Text Search to Get Results Like Google
Rails Pg::Undefinedtable: Error: Missing From-Clause Entry for Table
SQL Server - Update Column from Data in the Same Table
Comparison Operator in Pyspark (Not Equal/ !=)
Ruby on Rails Where Query with Nesting Relations
Try_Convert Fails on SQL Server 2012
Cycle Detection with Recursive Subquery Factoring
How to Generate a Permutations or Combinations of N Rows in M Columns
How to Set The Starting Point for The Primary Key (Id) Column in Postgres via a Rails Migration
Convert Datetime to Unix Timestamp