MYSQL detecting overlap date range in the table

Question

I have a table with the following data.

CREATE TABLE DiscountFormula (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
, dateFrom date
, dateUntil date
);

INSERT INTO DiscountFormula
  (dateFrom, dateUntil)
VALUES
  ("2020-06-01", "2020-06-10")
, ("2020-06-03", "2020-06-13")
, ("2020-06-25", "2020-06-29")
, ("2020-07-01", "2020-07-01")
, ("2020-07-03", "2020-07-03")
;

fiddle demo

id    dateFrom     dateUtil     
====  ========     ========     
1     2020-06-01   2020-06-10  
2     2020-06-03   2020-06-13  
3     2020-06-25   2020-06-29
4     2020-07-01   2020-07-01
5     2020-07-03   2020-07-03

As you can see id 1 and 2 is overlap on a date range. I want to create a query to detect if any overlap date range in the table. Meaning if count > 0 have overlap data so i can throw an alert message. What I have try, but seem is not working.

SELECT * #count(*) as TOTAL
FROM DiscountFormula A
JOIN DiscountFormula B 
ON (A.dateFrom >= B.dateFrom AND A.dateFrom <= B.dateUntil)
OR (A.dateUntil >= B.dateFrom AND A.dateUntil <= B.dateUntil)

Tim Biegeleisen · Accepted Answer · 2020-06-22 06:21:55Z

3

Your formula for detecting overlapping ranges is slightly off. It should be this:

WHERE A.dateFrom < B.dateUntil
  AND A.dateUntil > B.dateFrom
  AND A.id <> B.id

Updated query:

SELECT COUNT(*)
FROM DiscountFormula A
INNER JOIN DiscountFormula B
    ON A.dateFrom < B.dateUntil AND A.dateUntil > B.dateFrom AND
       A.id <> B.id;

edited Jun 22, 2020 at 6:21

answered Jun 22, 2020 at 6:09

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nixoderm Over a year ago

but why if I add WHERE A.id IN (2,3,4,5) it still count > 0. this id's is not contain overlap date range?

Tim Biegeleisen Over a year ago

@Nixoderm Good catch...you should also assert that A.id <> B.id, to avoid counting a single row as overlapping with itself during the self join.

Gordon Linoff · Accepted Answer · 2020-06-22 11:47:42Z

0

I would recommend using a cumulative maximum:

select count(*)
from (select df.*,
             max(df.dateuntil) over (order by df.datefrom
                                     rows between unbounded preceding and 1 preceding
                                    ) as prev_dateuntil
      from DiscountFormula df
     ) df
where prev_dateuntil > datefrom;

This should be much faster than a self-join. If count(*) is 0 then there are no overlaps.

Actually, I think a lag() is sufficient for this problem:

select count(*)
from (select df.*,
             lag(df.dateuntil) over (order by df.datefrom) as prev_dateuntil
      from DiscountFormula df
     ) df
where prev_dateuntil > datefrom;

answered Jun 22, 2020 at 11:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MYSQL detecting overlap date range in the table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related