Solved

SQL Query for Overlapped Period

Posted on 2010-09-09
12
722 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now