Find overlapping of dates: How to find the dates of the user overlaps with other users in mysql?
If the date of the users overlaps with other users then it should update as Overlap for that user else as No_overlap in a new column.
Code for creating table:
CREATE TABLE IF NOT EXISTS dates_range ( `User` VARCHAR(2) CHARACTER SET utf8, `start_date` DATE, `end_date` DATE ); INSERT INTO dates_range VALUES ('U1','2020-01-01','2020-01-31'), ('U2','2020-01-16','2020-01-26'), ('U3','2020-01-28','2020-02-06'), ('U4','2020-02-16','2020-02-26');
Its output is:
User start_date end_date U1 2020-01-01 2020-01-31 U2 2020-01-16 2020-01-26 U3 2020-01-28 2020-02-06 U4 2020-02-16 2020-02-26
Want the output like:
User status U1 Overlap U2 Overlap U3 Overlap U4 No_Overlap
Please help with the easiest method to find the overlapping, I’m naive in sql and having difficulty in understanding and finding overlapping of dates.
Using exists logic along with the formula for overlapping date ranges we can try:
SELECT User, CASE WHEN EXISTS (SELECT 1 FROM dates_range dr2 WHERE dr2.start_date < dr1.end_date AND dr2.end_date > dr1.start_date AND dr2.User <> dr1.User) THEN 'Overlap' ELSE 'No_Overlap' END AS status FROM dates_range dr1 ORDER BY User;
Answered By – Tim Biegeleisen