Calculate Missing Date Ranges and Overlapping Date Ranges Between Two Dates

Calculate missing date ranges and overlapping date ranges between two dates

It's a little variation of the function to flatten intersecting timespans in SQL Server:

• Flattening timespans: SQL Server

It's one of the rare cases when cursor-based approach in SQL Server is faster the a set-based one:

---

CREATE FUNCTION mytable(@p_from DATETIME, @p_till DATETIME)
RETURNS @t TABLE
        (
        q_type VARCHAR(20) NOT NULL,
        q_start DATETIME NOT NULL,
        q_end DATETIME NOT NULL
        )
AS
BEGIN
        DECLARE @qs DATETIME
        DECLARE @qe DATETIME
        DECLARE @ms DATETIME
        DECLARE @me DATETIME
        DECLARE cr_span CURSOR FAST_FORWARD
        FOR
        SELECT  startDate, endDate
        FROM    mytable
        WHERE   startDate BETWEEN @p_from AND @p_till
        ORDER BY
                startDate 
        OPEN    cr_span
        FETCH   NEXT
        FROM    cr_span
        INTO    @qs, @qe
        SET @ms = @qs
        SET @me = @qe
        WHILE @@FETCH_STATUS = 0
        BEGIN
                FETCH   NEXT
                FROM    cr_span
                INTO    @qs, @qe
                IF @qs > @me
                BEGIN
                        INSERT
                        INTO    @t
                        VALUES ('overlap', @ms, @me)
                        INSERT
                        INTO    @t
                        VALUES ('gap', @me, @qs)
                        SET @ms = @qs
                END
                SET @me = CASE WHEN @qe > @me THEN @qe ELSE @me END
        END
        IF @ms IS NOT NULL
        BEGIN
                INSERT
                INTO    @t
                VALUES  (@ms, @me)
        END
        CLOSE   cr_span
        RETURN
END
GO

This function compresses each contiguous set of intersecting ranges into one range, and returns both the range and the following gap.

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? Start A: StartB) <= (EndA < EndB? EndA: EndB)

Get overlapping dates between two date ranges (in columns) - WITHOUT creating database objects

SELECT
  yourTable.EmployeeID,
  DATEADD(DAY, calendar.date_id, yourTable.PeriodStart)
FROM
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY the_primary_key) - 1 AS date_id    
  FROM
    any_big_enough_table
)
  AS calendar
INNER JOIN
  yourTable
    ON  calendar.date_id <= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.PeriodEnd)
    AND calendar.date_id >= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.LOAStartDate)
    AND calendar.date_id <= DATEDIFF(DAY, yourTable.PeriodStart, yourTable.LOAEndDate)

Please excuse typos, I'm on my phone.

Find the optimal pair of date ranges from a list determined by overlapping days

Pair off all date ranges with a common id and find the ones that have
The longest stretch of common days. Ties are possible so handle those as necessary.

with overlap as (
    select p1.id, overlapdays, maxstart, minend,
        dense_rank() over (partition by p1.id order by overlapdays desc) as dr
    from portfoliodates p1 inner join portfoliodates p2
        on p2.id = p1.id and (
              p2.startdate > p1.startdate
           or p2.startdate = p1.startdate and p2.enddate > p1.enddate
        )
        cross apply (values (
          case when p1.startdate > p2.startdate then p1.startdate else p2.startdate end,
          case when p1.enddate   < p2.enddate   then p1.enddate   else p2.enddate   end
        )) v1(maxstart, minend)
        cross apply (values (datediff(day, maxstart, minend))) v2(overlapdays)
    where overlapdays >= 0
)
select * from overlap where dr = 1;

https://dbfiddle.uk/LhgVL7z9

SQL Finding Missing Dates Between Ranges

Try this:

WITH
  a AS (
    SELECT
      SourceSystem, FromDate, ToDate,
      LEAD(FromDate) OVER(
        PARTITION BY SourceSystem
        ORDER BY RunDate
      ) AS NextDate
    FROM #temptable
  )
SELECT
  SourceSystem,
  DATEADD(DAY, 1, ToDate) AS GapBeg,
  DATEADD(DAY, -1, NextDate) AS GapFin
FROM a
WHERE
  NextDate IS NOT NULL AND
  DATEADD(DAY, -2, NextDate) >= ToDate;

Result:

+--------------+------------+------------+
| SourceSystem |   GapBeg   |   GapFin   |
+--------------+------------+------------+
| AAP          | 2021-06-03 | 2021-06-03 |
| ILG          | 2021-06-02 | 2021-06-04 |
+--------------+------------+------------+

db-fiddle

Problem calculating overlapping date ranges

Do not look at the intervals, look only at their ends.

You have a bunch of starting moments and a bunch of ending moments. Imagine that starting moments are red and ending moments are blue. Or imagine that starting moments are opening braces and ending moments are closing braces.

Put them all together in a list. Sort the list from earliest to latest, ignoring the colour.

Now take a counter set to zero with you, and walk down the list. When you see a red moment, increment the counter. When you see a blue moment, decrement the counter. When the counter value goes from 0 to 1, output "start" and the current time. When the counter value goes from 1 to 0, output "end" and the current time. If the counter value drops below 0, output "Houston, we have a problem". You should end with your counter at 0 and a bunch of nice non-overlapping intervals.

This is the good old brace counting algorithm.

Illustration.

 A bunch of overlapping intervals:

 (-------------------) 
                       (----------------------)           
                                                          (---)
       (---------------------)                       
                                                     (-----------------)

 A bunch of interval ends:

 (-----(-------------)-(-----)----------------)      (----(---)--------)

Get distinct consecutive date ranges from overlapping date ranges

The logic is:

• at the beginning of an interval add its value to a cumulative sum
• at the end of an interval substract its value from this sum
• but in order to sweep the dateline, we'll have to collect al the (unique) date/time stamps, either start or stop.

So the point is: convert the data from a series of intervals to a series of (start/stop) events, and aggregate over these.

---

-- \i tmp.sql

create table coinsonperiod(
  id serial,
  startdate date,
  enddate date,
  coins integer
);
insert into coinsonperiod (startdate, enddate, coins) values
  ('2018-01-01','2018-01-31', 80)
, ('2018-01-07','2018-01-10', 10)
, ('2018-01-07','2018-01-31', 10)
, ('2018-01-11','2018-01-31', 5)
, ('2018-01-25','2018-01-27', 5)
, ('2018-02-02','2018-02-23', 100)
        ;

WITH changes AS (
    SELECT startdate AS tickdate , coins
            , 1 AS cover
    FROM coinsonperiod
    UNION ALL
    -- add 1 day to convert to half-open intervals
    SELECT 1+enddate AS tickdate, -1* coins
            , -1 AS cover
    FROM coinsonperiod
    )
, sumchanges  AS (
        SELECT tickdate, SUM(coins) AS change, SUM(cover) AS cover
        FROM changes
        GROUP BY tickdate
        )
, aggregated AS (
        SELECT
        tickdate AS startdate
        , lead(tickdate) over www AS enddate
        , sum(change) OVER www AS cash
          -- number of covered intervals
        , sum(cover) OVER www AS cover
        FROM sumchanges
        WINDOW www AS (ORDER BY tickdate)
        )
             -- substract one day from enddate to correct back to closed intervals
SELECT startdate, enddate-1 AS enddate, cash, cover
FROM aggregated
WHERE cover > 0
ORDER BY startdate
        ;

---

Related Topics