SQL Query for Overlapped Period
Posted on 2010-09-09
I have a MySQL Table that have scheduled task list. Example
Date From To Task Description
09/12/2010 10:00 11:00 Watch TV
09/12/2010 11:00 12:30 Study
09/12/2010 12:30 13:00 Lunch
Same for whole week.
How user Enter Data:
user select start date (example: 09/12/2010) and end date (example: 09/17/2010), after that they select start time (10:00) and end time (11:00) and enter task description.
This enter data for all selected days as individual record, as I mentioned above.
How to find overlapped task?
Now Question is:
If any user select date range for start date and end date (it can be any date range) and select start time and end time, then we need to show them task overlapped between selected period.
example if user select date range from 09/14/2010 to 09/18/2010 (of any range) and select start-end time as (10:30 to 11:45) then overlapped task will be from 09/14/2010 to 09/17/2010, and come between selected time period.
I hope you all understand my requested.
One simple way to use looping for one by one single day and extract records and combine them at the end of query.
Is There any tricky way available that can sort my problem in some line of queries (single query)?
You can provide me example in MySQL or SQL Server Query format. I am not strictly bind to MySQL, I will convert it accordingly.