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

0

Issue

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.

Solution

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

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More