troubleshooting Question

SQL Query for Overlapped Period

Avatar of Mohit Vijay
Mohit VijayFlag for India asked on
MySQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
12 Comments3 Solutions791 ViewsLast Modified:

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.

Thanks beforehand.
Join our community to see this answer!
Unlock 3 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros