CODEWITHSUNDEEP
mysqlsqldatabase
user3309825
user3309825

Reputation: 63

Overlapping time intervals

Giving the following table, I would like to discover the time interval where an overlap occurs between 2 or more users, and then record those times along with the users involved in the overlap. I have no clue of how I should approach this, so any help will be appreciated

Table1:

UserID  StartDate   EndDate
1       15:00       22:00
2       12:00       18:00
3       02:00       09:00
4       07:00       13:00

Expected Result:

StartDate       EndDate     Users
15:00           18:00       1,2
07:00           09:00       3,4
12:00           13:00       2,4

Upvotes: 3

Views: 174

Answers (1)

cy3er
cy3er

Reputation: 1699

Here it is in SQL Fiddle.

But if you want some explanation:

  1. I joined the table twice on those rows that has overlapping:

    from over t1 join over t2 on t1.START < t2.END and t1.START > t2.START

  2. then selected simply the highest start and lowest end dates:

    greatest(t1.START, t2.START), least(t1.END, t2.END)

  3. ... and in a nested select grouped the ids of those rows that covers the iterval:

    t3.START <= greatest(t1.START, t2.START) and t3.END >= least(t1.END, t2.END)

Upvotes: 4

Related Questions