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") ;
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
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)
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
SELECT COUNT(*) FROM DiscountFormula A INNER JOIN DiscountFormula B ON A.dateFrom < B.dateUntil AND A.dateUntil > B.dateFrom AND A.id <> B.id;
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
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;