Solved

SQL Query for Overlapped Period

Posted on 2010-09-09
12
729 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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