Solved

SQL Query for Overlapped Period

Posted on 2010-09-09
12
732 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Mohit Vijay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 10

Expert Comment

by:james-ct16
ID: 33643536
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

0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33643556
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.
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33643623
Please find Attached example Data Screenshot (this is datagrid view)
OverlappedTaskSample.JPG
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33643653
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)))
0
 
LVL 10

Expert Comment

by:james-ct16
ID: 33643659
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
0
 
LVL 3

Assisted Solution

by:jchook
jchook earned 167 total points
ID: 33643661
I think the BETWEEN operator will come in handy for you here.

Unless I am misunderstanding, it seems best if to store corresponding dates and times together in the database as a single column. The DATETIME type is perfect for this in MySQL and is very flexible, though your choice should ultimately be based upon performance requirements. A simple INT or BIGINT would be faster and quite sufficient in many cases  (e.g. storing the number of seconds or milliseconds from the UNIX Epoch). In either case, each row would have two time-related columns: begin and end.

Your MySQL query for tasks that overlap the area between two times X and Y would be:

SELECT * FROM `tasks_table`
WHERE (
  ( begin <= X AND end > X )  OR
  ( begin BETWEEN X AND Y )
)

In English, we can say this:
" Show tasks which begin before a starting time X, and end strictly after X
OR show me tasks that begin between the starting time X and its ending time, Y. "


Some References:
DATETIME => http://dev.mysql.com/doc/refman/5.0/en/datetime.html
BETWEEN => http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

-- MySQL Code

SELECT * FROM `tasks_table`
WHERE (
  ( begin <= X AND end > X )  OR
  ( begin BETWEEN X AND Y )
)

Open in new window

0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33643663
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)))
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33643673
@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?
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 333 total points
ID: 33644349
I don't believe between gets you there correctly.
Because for task 3-4pm, and period 4-5pm, 4 is "between 4 and 5 (inclusive of both ends)".

To simplify things an overlap of two ranges A and B occurs when
"one period starts in the middle of the other", not including the end time.
The "not including" part is always better written as "< boundary" rather than "<= some time very close to boundary", e.g.
"< 2" rather than "<= 1.99999999999"

Therefore, you can use this:

SELECT * From TaskTable
WHERE UserID = 2
-- date range pre-filter, changed the end bound test
AND TaskStarttime >= '2010-08-25' AND Taskendtime < '2010-08-28'
AND (
    (TIME(TaskStarttime) >= '10:30' AND TIME(TaskStarttime) < '11:30') -- task starts within user entered range
    OR  
    ('10:30' >= TIME(TaskStarttime) >=  AND '10:30' < TIME(TaskStarttime)) -- user entered range starts within task
    )
0
 
LVL 8

Author Comment

by:Mohit Vijay
ID: 33644363
This is the reason, I used

'10:31:00' AND '11:29:59'
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 333 total points
ID: 33644841
I know, that is why I recommend
The "not including" part is always better written as "< boundary" rather than "<= some time very close to boundary"

It may be correct, but it may not be, for instance, why use 10:31 and not 10:30:01 which is equally 1 second away?
It should be 10:30 anyway to be inclusive.
Time precisions changes, and depending on your data, 11:29 may cut it, or 11:29:59 or 11:29:59.999 when using "<="
Yet if you make it a habit to use < 11:30 for any data, it will never fail to be correct.

Just a thought from experience.
0
 
LVL 8

Author Closing Comment

by:Mohit Vijay
ID: 33652429
I am using commend id http:#a33643663. It worked for me, but other accepted answer was also helpful.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 36
database connection error mysql stops 7 30
mysql qry 1 25
Why is this SQL bringing back extra rows? (parsing XML data) 4 39
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question