How to detect overlapping Time Entries

Hello,

I am trying to create a SQL Statement which will identify if an entry can be added to a table or not. My table consists of 4 fields which are:

. UserID (Integer)
. StartTime (datetime)
. EndTime (datetime)
. Activity (varchar)

This is a timesheet application. I am trying to identify if a time entered by a user is valid or not. Basically, times cannot overlap. I'm trying to figure out how to code for the following conditions:

Assume an entry already exists for User 1 as follows:

. UserID: 1
. StartTime: 2006-12-30 08:00:00
. EndTime: 2006-12-30 08:15:00
. Activity: Test

I want to make sure that the following entries cannot be added by that user because they would overlap the existing entry:

. StartTime: 2006-12-30 07:50:00
. EndTime: 2006-12-30 08:05:00

OR

. StartTime: 2006-12-30 07:45:00
. EndTime: 2006-12-30 08:45:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:30:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:10:00

Any help is appreciated.

Thanks

Something like this...

select *
from timetrack_tbl
where
userid = 1
AND
(
(
'2006-12-30 07:50:00' between starttime and endtime
OR
'2006-12-30 08:05:00' between starttime and endtime
)
OR
(
'2006-12-30 07:50:00' <= starttime
AND
'2006-12-30 08:05:00' >= endtime
)
)
If you are going to be doing this continually, it might be a good choice for a function with the above code. Create the function to accept the userid, starting and ending dates and return some value indicating whether there is an overlap.

Example:
select dbo.CheckForOverlap(userid, '2006-12-30 07:50:00', '2006-12-30 08:05:00')

return something from the function that tells you if there is an overlap (like a bit, 1 = Overlap, 0 = No Overlap

|||

That worked like a charm.

Thank you so much for the quick response.

-- Val