so here is the description.
There is a table with a time intervals to spend for the specific tasks:
| id | start | finish |
|---|---|---|
| 1 | 07:00:00 | 11:00:00 |
| 2 | 12:30:00 | 17:00:00 |
| 3 | 11:30:00 | 14:00:00 |
| 4 | 09:30:00 | 11:30:00 |
| 5 | 10:00:00 | 15:00:00 |
| 6 | 09:00:00 | 13:30:00 |
| 7 | 07:00:00 | 11:30:00 |
These tasks can't be done simultaneously. I need to find how many minimum days I should spend to finish the all tasks.
In this table, I can spend a minimum of 5 days to finish the tasks.
day 1 => task 1 and task 2
day 2 => task 4 and task 3
day 3 => task 5
day 4 => task 6
day 5 => task 7
So the result should be 5. I hope it was clear, thank you in advance!
CodePudding user response：
SELECT COUNT(*)
FROM test t1
JOIN test t2 ON
-- these 2 conditions checks for overlapping
t1.start < t2.finish
AND t2.start < t1.finish
-- this condition simply decreases the amount of combined rows
-- to be examined, it does not effect the output
AND t1.id <= t2.id
GROUP BY t1.id
-- retrieve only maximal value
ORDER BY 1 DESC LIMIT 1
You must simply count the maximal amount of simultaneous tasks. It is enough to check each task start time.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ee3034a307c32212db365ba06f654f9f