Solved

SQL Query to detech any overlap between 2 time zone range

Posted on 2010-08-25
8
678 Views
Last Modified: 2012-05-10
I have a  sql procedure that needs to check for any overlaps between 2 time ranges. ie if u look at the form attached. You will see that the START time options START_HOUR and START_MINUTE
and the END time option is END_HOUR and END_MINUTE.

now for illustration purpose, let me assume that the START_HOUR parameter that is sent to the SQL stored procedure is 8 and START_MINUTE is 1 (for 00) . Similarly the END_HOUR is 9 and END_MINUTE is 1(for 00). So the time range is 8:00 - 9:00

Now i want to check if any time range choosen falls in this range.(ie in between 8:00 and 9:00)

This can happen to any list choosen. Hence if the user choose the Input form and enter 8:15 - 9:00, the store procedure should tell me that a overlap has occured.

How do i handle such situation and check for overlaps.
8-25-2010-9-35-19-PM.png
0
Comment
Question by:TECH_NET
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 2

Expert Comment

by:GLoad
ID: 33527760
You will need to ammend this query to use your variables, but this is the principal.

You should also ensure your dates are comparible and are stored as datetimes on the same day or serial times (in seconds or something).

If the count on this is > 0 you have a conflict. This will cover you for times that start and finish inside the entered, start and finish before and after the entered or overlap in any way.


SELECT
  COUNT(*)
FROM
  SESSIONS
WHERE
  (@StartDate BETWEEN SESSIONS.STARTDATE AND SESSIONS.ENDDATE)
  OR (@EndDate BETWEEN SESSIONS.STARTDATE AND SESSIONS.ENDDATE)
  OR (SESSIONS.STARTDATE BETWEEN @StartDate AND @EndDate)
  OR (SESSIONS.ENDDATE BETWEEN @StartDate AND @EndDate)

Open in new window

0
 
LVL 8

Expert Comment

by:infolurk
ID: 33527777
Looks like you want to set a trigger.

Using the between function in a query should do the trick i.e
select session identifier
from sessions table s
 join
start/finish times table sf
where sf.start between s.start and s.finish or sf.finish between s.start and s.finish

It would be simpler if you had the start and finish times in the sessions table in seperate fields. Otherwise you will have to create variables to grab the required information from the one fiels.
0
 
LVL 8

Expert Comment

by:infolurk
ID: 33527778
*field*
0
 

Author Comment

by:TECH_NET
ID: 33527863
I do not use the date in the comparison. I need to use the way it is currently stored.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:TECH_NET
ID: 33527894
infolurk: I do have the start and finish times in the sessions table in seperate columns. Refer to the attached image.

In your query u have used start/finish timestable. what s this table.?
8-25-2010-11-22-27-PM.png
0
 
LVL 2

Expert Comment

by:stevebobs
ID: 33538749
--Now i want to check if any time range choosen falls in this range.(ie in between 8:00 and 9:00)

i think it would be cleaner to convert the daterange (like 1:00-2.15)  to a number range first.
Also you need to specify whether the comparison is inclusive or not, ie most users would enter 1:00 - 2:00 and 2:00-3:15  in preference to 1:00 - 1:59 and  2:00-3:14

Could use CAST or CONVERT functions but these can be fiddly; i tend to use the most readable and least fussy option...
ie
something like

-- input vars, hours and dates
@sth INT
@stm INT
@fth INT
@ftm INT

--intermediate vars
DECLARE @startdate INT
DECLARE @finishdate INT

--make into number
@startdate = @sth*60 + @stm
@finishdate = @fth*60 + @ftm

-- then can compare using normal numeric operators <, <=, >=, BETWEEN etc to compare against other stored dates...

--
Note: I would also store existing calculated values in a calculated column for the comparison values rather than converting for each check - also easier to debug this way as mathematical issues are generally unambiguous
0
 

Author Comment

by:TECH_NET
ID: 33538922
stevebobs: I had indeed progressed a similar way but adding a new column in the view which converts the 9:10-10:30 AM  as 9*60 + 10 for Morning sessions and 9*60 + 720 + 30 for 9:00 -10:30 PM for the evening session.

Note: (720=12*60)

Now the problem is with 2 additional columns with computed minutes, how do i do the comparison.

eg:
for 8:05 AM to 09:00 AM the START_TIME column value would be  485 and the END_TIME would be 540

now i choose another entry

8:40 AM - 9:40 AM which we know clearly overlaps the previous time range.

Our calculated results would be START_TIME column value=520 and END_TIME column value=580


SELECT COUNT(*) AS TOTAL FROM TIMETABLE_VIEW
WHERE
( START_TIME   BETWEEN @START_TIME_ID AND  @END_TIME_ID ) OR
( END_TIME   BETWEEN @START_TIME_ID AND  @END_TIME_ID )  

WHERE START_TIME_ID would be 520 and END_TIME_ID=580


This query fails. I am stuck here.

0
 
LVL 2

Accepted Solution

by:
stevebobs earned 500 total points
ID: 33539335
-- surely this would suffice...?
SELECT COUNT(*) AS TOTAL FROM TIMETABLE_VIEW
WHERE
( START_TIME   >= @START_TIME_ID)  AND (END_TIME < @END_TIME_ID )

0

Featured Post

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query help 7 82
Stored procedure query with if 27 32
SQL Query 2 57
display data in text field from data base for updating 6 28
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 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

18 Experts available now in Live!

Get 1:1 Help Now