asked on 9/10/2010

SQL Query for Overlapped Period

Hello,

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.

MySQL Server Microsoft SQL Server 2008 Microsoft SQL Server 2005

Last Comment

Mohit Vijay

9/11/2010

james-ct16

9/10/2010

Howdy

Hopefully something like the following is what you are after. This is a tsql example.

Regards

James

declare @tasks table
(
taskID		int,
startDate	datetime,
endDate		datetime,
task		varchar(50)
)

insert into @tasks values (1,'01/01/2010 10:00:00', '01/01/2010 10:30:00', 'DoSomething')
insert into @tasks values (2,'01/01/2010 11:00:00', '01/01/2010 11:30:00', 'DoSomething 1')
insert into @tasks values (3,'01/01/2010 11:30:01', '01/01/2010 12:30:00', 'DoSomething 2')
insert into @tasks values (4, '01/01/2010 12:29:00', '01/01/2010 12:35:00', 'Overlap')

select * from @tasks t1
cross join @tasks t2
where 
(
	t1.startDate between t2.startDate and t2.endDate
	or
	t1.endDate between t2.startDate and t2.endDate
)

and t1.taskID <> t2.taskID

Open in new window

Mohit Vijay

9/10/2010

ASKER

user will enter date and time and then we need to show alert that task is overlapped. I think above example for filter overlapped task those are already entered.
BTW Its a good suggestion, I am going to try something like this.

Mohit Vijay

9/10/2010

ASKER

Please find Attached example Data Screenshot (this is datagrid view)
OverlappedTaskSample.JPG

Mohit Vijay

9/10/2010

ASKER

I think I found my solution, is there any other better way (System is related to MySQL)

user entered date range from 08/25/2010 to 08/27/2010 and time 10:30 to 11:30

SELECT * WHERE 1=1 AND UserID = 2 AND TaskStarttime >=
'2010-08-25 00:00:00' AND Taskendtime <= '2010-08-27 23:59:59' AND ((TIME(TaskStarttime) BETWEEN '10:31:00' AND '11:29:59') OR (TIME(TaskEndTime) BETWEEN '10:31:00' AND '11:29:59') OR ('10:31:00' BETWEEN TIME(TaskStarttime) AND TIME(TaskEndTime)) OR ('11:29:59' BETWEEN TIME(TaskStarttime) AND TIME(TaskEndTime)))

james-ct16

9/10/2010

Howdy
This looks more like you are trying to perform validation on your datagrid than a database issue. So what you probably want to be doing is performing a linq query equivalent to the one posted earlier on your cell validating event. Very quick introduction to linq can be found here http://www.15seconds.com/issue/060713.htm

Regards

James

SOLUTION

jchook

9/10/2010

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Mohit Vijay

9/10/2010

ASKER

I edited above query, thats why I missed many things like "FROM" "TABLE NAME" :)

but logic is in where clause

SELECT * From TaskTable WHERE UserID = 2 AND TaskStarttime >=
'2010-08-25 00:00:00' AND Taskendtime <= '2010-08-27 23:59:59' AND ((TIME(TaskStarttime) BETWEEN '10:31:00' AND '11:29:59') OR (TIME(TaskEndTime) BETWEEN '10:31:00' AND '11:29:59') OR ('10:31:00' BETWEEN TIME(TaskStarttime) AND TIME(TaskEndTime)) OR ('11:29:59' BETWEEN TIME(TaskStarttime) AND TIME(TaskEndTime)))

Mohit Vijay

9/10/2010

ASKER

@james-ct16

LinQ Query looks good. Yes, I want to use it, if possible and allows me.

Question, I justed show datagrid for data example, I am applying validation on other page where I dont have any datagrid or any control that have all data. I have to extract it from Database. Any why to use LINQ in this condition?

SOLUTION