Find overlapping of dates: How to find the dates of the user overlaps with other users in mysql?

user_17196610 0
Viewed 46

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.

mysql sql
1 Answers

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;
user_18632290
Related