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 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)
Santiago Trujillo
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;
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;