Query to Check Overlapping Ranges in SQL Server

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)

Query to check overlapping ranges in sql server?

The easiest way to find an overlap is like this:

IF EXISTS (SELECT 1 FROM table WHERE @myValueLo <= ExistingRangeEnd AND @myValueHi >= ExistingRangeStart)
  -- Overlaps
ELSE
  -- Doesn't overlap

This can be shown to work if you compare the condition above against each of the bars in the diagram below:

Existing range:         |-------------------|
Overlaps:       |-------------|       |------------|
                |----------------------------------|
                           |-------------|
Not overlaps:   |-----|                       |----|

in all the overlap cases, both these tests are true:

• the start date of the existing range is always before the end date of the new range
• the end date of the existing range is after the start date of the new range

Those that don't overlap fail one or other of this test.

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 :)

What is a simple and efficient way to find rows with time-interval overlaps in SQL?

SELECT * 
FROM table1,table2 
WHERE table2.start <= table1.end 
AND (table2.end IS NULL OR table2.end >= table1.start)

Find Overlapping Dates and Return Overlapping Records

Assuming that your #Overlap table has a primary key (or unique key) TableId

You could use this following query

SELECT * FROM #Overlap o
WHERE EXISTS
(
    SELECT 1 FROM #Overlap o2
    WHERE o2.SubType = o.SubType
    AND o2.Cause = o.Cause
    AND o2.CircuitID = o.CircuitID
    AND (
            o2.BegDate BETWEEN o.BegDate AND o.EndDate
            OR o2.EndDate BETWEEN o.BegDate AND o.EndDate
            OR o.BegDate BETWEEN o2.BegDate AND o2.EndDate
            OR o.EndDate BETWEEN o2.BegDate AND o2.EndDate
        )
    AND o2.TableId != o.TableId
)
ORDER BY o.SubType, o.Cause, o.BegDate

Demo link: http://rextester.com/KBFX30109

As @HABO suggestion, you also can use to check overlapping

    AND o2.BegDate <= o.EndDate 
    AND o.BegDate <= o2.EndDate 

Find common date range from a set of overlapping date ranges

This answer counts the number of overlapping intervals. It assumes that date ranges having same EID do not overlap. Below is the query with explanation inline:

DECLARE @EventsTBL TABLE (PID INT, EID INT, StartDate DATETIME, EndDate DATETIME);
INSERT INTO @EventsTBL VALUES
(13579, 1,  '01 Jan 2018', '31 Mar 2019'),
(13579, 2,  '01 Feb 2018', '31 May 2018'),
(13579, 2,  '01 Jul 2018', '31 Jan 2019'),
(13579, 7,  '01 Mar 2018', '31 Mar 2019'),
(13579, 5,  '01 Feb 2018', '30 Apr 2018'),
(13579, 5,  '01 Oct 2018', '31 Mar 2019'),
(13579, 8,  '01 Jan 2018', '30 Apr 2018'),
(13579, 8,  '01 Jun 2018', '31 Dec 2018'),
(13579, 13, '01 Jan 2018', '31 Mar 2019'),
(13579, 6,  '01 Apr 2018', '31 May 2018'),
(13579, 6,  '01 Sep 2018', '30 Nov 2018'),
(13579, 4,  '01 Feb 2018', '31 Jan 2019'),
(13579, 19, '01 Mar 2018', '31 Jul 2018'),
(13579, 19, '01 Oct 2018', '28 Feb 2019'),
(13570, 16, '01 Feb 2018', '30 Jun 2018'),
(13570, 16, '01 Aug 2018', '31 Aug 2018'),
(13570, 16, '01 Oct 2018', '28 Feb 2019'),
(13570, 23, '01 Mar 2018', '30 Jun 2018'),
(13570, 23, '01 Nov 2018', '31 Jan 2019');

WITH cte1 AS (
    /*
     * augment the data with the number of distinct EID per PID
     * we will need this later
     */
    SELECT e.PID, a.EIDCount, StartDate, EndDate
    FROM @EventsTBL AS e
    JOIN (
        SELECT PID, COUNT(DISTINCT EID) AS EIDCount
        FROM @EventsTBL
        GROUP BY PID
    ) AS a ON e.PID = a.PID
), cte2 AS (
    /*
     * build a list of "points in time" at which an event started or ended
     * and the number concurrent events changed
     * the zero value rows are required!
     */
    SELECT PID, EIDCount, StartDate AS pdate, 1 AS pval
    FROM cte1
    UNION ALL
    SELECT PID, EIDCount, EndDate, 0
    FROM cte1
    UNION ALL
    SELECT PID, EIDCount , DATEADD(DAY, 1, EndDate), -1
    FROM cte1
), cte3 AS (
    /*
     * calculate running sum of pval over dates; minus ones first
     */
    SELECT PID, EIDCount, pdate, SUM(pval) OVER (PARTITION BY PID ORDER BY pdate, pval) AS evtcount
    FROM cte2
), cte4 AS (
    /*
     * consolidate data for same dates and we are done with the main part
     */
    SELECT PID, EIDCount, pdate, MAX(evtcount) AS evtcount
    FROM cte3
    GROUP BY PID, EIDCount, pdate
), cte5 AS (
    /*
     * assign "change flag" to rows where number of concurrent events
     * enters or exits the required count w.r.t. previous row
     */
    SELECT PID, EIDCount, pdate, evtcount, CASE
        WHEN evtcount < EIDCount AND LAG(evtcount) OVER (PARTITION BY PID ORDER BY pdate) < EIDCount THEN 0
        WHEN evtcount = EIDCount AND LAG(evtcount) OVER (PARTITION BY PID ORDER BY pdate) = EIDCount THEN 0
        ELSE 1
    END AS chg
    FROM cte4
), cte6 AS (
    /*
     * convert "change flag" to "group numbers" over consecutive rows using running sum
     */
    SELECT PID, EIDCount, pdate, evtcount, SUM(chg) OVER (PARTITION BY PID ORDER BY pdate) AS grp
    FROM cte5
)
/*
 * group rows by pid and group numbers
 */
SELECT PID, MIN(pdate) AS StartDate, MAX(pdate) AS EndDate
FROM cte6
WHERE evtcount = EIDCount
GROUP BY PID, grp
ORDER BY PID, StartDate

Demo on db<>fiddle

How to find overlapping date ranges in Postgresql between multiple rows?

It won't be fast, as every row has to be matched with every other row:

SELECT a.*, b.*
FROM mytable AS a
   JOIN mytable AS b
      ON daterange(a.valid_from, a.valid_to) && daterange(b.valid_from, b.valid_to)
WHERE (a.valid_from, a.valid_to) <= (b.valid_from, b.valid_to);

It might be better to have an exclusion constraint on the table that prevents such data from being added in the first place.

Determine max number of overlapping DATETIME ranges

This answer is for MySQL 8.0 that contains window functions. The core of the solution will be the following query that finds a number of overlapping intervals for every interesting interval in the data:

select t2.startDt, t2.endDt, count(*) overlaps_count
from
(
    select lag(t1.dt) over (order by t1.dt) startDt, t1.dt endDt
    from
    (
        select startt dt from data
        union
        select endt dt from data
    ) t1
) t2
join data on t2.startDt < data.endt and t2.endDt > data.startt
group by t2.startDt, t2.endDt

DBFiddle DEMO

Once you have this result (let call it Overlap table) then you may easily find the maximum for an input interval as follows

with Overlap as
(
   -- the query above
)
select max(overlaps_count)
from Overlap 
where @start < endDt and @end > startDt

---

Related Topics