• Home
  • Jobs
  • Courses
  • Teachers
  • For business
  • Blog
  • ES/EN

0

40
Views
MYSQL detecting overlap date range in the table

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)
14 days ago ·

Santiago Trujillo

2 answers
Answer question

0

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;
14 days ago · Santiago Trujillo Report

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;
14 days ago · Santiago Trujillo Report
Answer question
Find remote jobs
Loading

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post job Plans Our process Sales
Legal
Terms and conditions Privacy policy
© 2022 PeakU Inc. All Rights Reserved.