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
